1

If I have a table A which has a composite (two column) foreign key on another table B, should I:

  • a) create a two-column key in B to match the foreign key in A?
  • b) or, MySQL automatically creates the 'missing key' in B to match A's foreign key?
  • c) or, if neither a) nor b) should I assume MySQL might do a table scan on inserting into A?

I'm trying to avoid dubious keys in B as my index already grows at an alarming rate. However, I do not want to penalize insertion/updates into A and would rather take the index growth hit.

Please advise.

  • 1
    Show us the definitions of the 2 tables (output of SHOW CREATE TABLE a ; and b) – ypercubeᵀᴹ Jul 31 '16 at 13:59
1

You seem to have two questions -- one about efficiency of FKs, one about composite keys.

FOREIGN KEYs are are inherently inefficient. This is because they involve a lookup in another table to check for consistency. This happens even if you have thoroughly debugged your application, or if there is not need to the check.

So, if efficiency is the question, I say "get rid of FKs".

FOREIGN KEYs implicitly create the index needed for the above check. This index could have been manually created. Either way, the index probably benefits some JOIN in some queries. That is efficient, although there might be an even better index than the one that the FK decided to create.

Check to see what the FK generated by doing

SHOW CREATE TABLE table_name

You also mentioned "dubious keys". Here's a short list of "redundant" indexes to watch out for: http://mysql.rjweb.org/doc.php/index_cookbook_mysql#redundant_excessive_indexes

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.