Efficiency of foreign keys 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.
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