4

I have a two tables with 1:1 relationship, one table is called questions with 14 fields and the other is called links with 3 fields, the foreign key is in the question table. The tables are both MyISAM.

In the links table the fields are: linkId, questionUrl and count

The reason for having the links table is that I can track clicks each time a user clicks on a link it increments the corresponding row.

Am I correct to leave this, or move the 3 fields from the links table to the questions table? My reasons for having a separate table is because the links table will be accessed and updated a lot more (each time any link is clicked).

Are there any performance issues or caveats for either?

  • well apparently it seem to be correct according to your need – Nawaz Sohail Nov 8 '16 at 9:57
  • Re terminology: The foreign key is in the links table. It references the key in the questions table. – philipxy Nov 26 '16 at 22:00
  • 1
    MyISAM doesn't support (check) foreign key constraints. – philipxy Nov 26 '16 at 22:22
2

1:1 is usually a 'wrong' design.

One exception is when some column (such as "Likes" or "click count") is very frequently incremented. Putting that in a different table offloads the main table, and cuts back on the incrementation from interfering with other operations (and vice versa).

When does the recommendation kick in? You would have to test it for yourself.

However, one thing can be said with a fair amount of certainty -- you really should switch from MyISAM to InnoDB. UPDATE locks the entire table for MyISAM. For Innodb it only locks the rows to be modified. This can make a big difference when updating lots of different rows, or other high-volume activity.

Conversion tips .

  • +1 for the excellent tip about table types. Any other reasons for 1:1 being wrong design. – Abu Nooh Nov 12 '16 at 10:59
  • Why bother having the columns split across two tables, when they could be be in a single table? This saves a JOIN later. – Rick James Nov 12 '16 at 16:43
  • 1
    Another use for using 1:1... Some column(s) is rarely present; put it(them) in another table; do LEFT JOIN when you need to put the parts back together. – Rick James Nov 12 '16 at 16:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.