principlesqlMinor
Performance on same table vs 1:1 relationship in MySQL
Viewed 0 times
samemysqlperformancetablerelationship
Problem
I have a two tables with 1:1 relationship, one table is called
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?
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?
Solution
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.
Conversion tips .
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 .
Context
StackExchange Database Administrators Q#154409, answer score: 4
Revisions (0)
No revisions yet.