0

Do we need to create an index for a foreign key while creating the table in InnoDB of MySQL?

1
1

An index is generated for each FOREIGN KEY definition unless there is already an obviously adequate index in existence.

The index is needed to prevent a full table scan to verify that a row being inserted won't have a key linking to nowhere.

For an index to be useable, the leftmost column(s) must match the FK definition. Selectivity is irrelevant.

3
  • I guess this brings up another question - does he mean an index on the table with the FK constraint, or the table referenced? If the latter I'll go ahead and delete my answer. – bbaird Aug 26 '20 at 20:42
  • 1
    @bbaird - Create two tables. Create a FK on one table. Then do SHOW CREATE TABLE for both tables. To further test, you could add a thousand rows to each table, and do SHOW TABLE STATUS -- the one with the index should show growth in "Index_length". (I'm not a fan of FKs, or else I would do this exercise.) – Rick James Aug 26 '20 at 22:46
  • The question is unclear, not your answer. – bbaird Aug 27 '20 at 1:37
1

Foreign keys are not automatically indexed in MySQL*. If you should index, the answer is always: "It depends."

If you are going to primarily search based on that column, it's more than likely it should form the first part of that table's primary key and there would be no need for an additional index.

If the values of that column are not particularly selective, adding an index won't improve performance as a table scan will still be required.

So as always, test and validate.

*This depends on where you are expecting the foreign key to be created, on the table with the FK constraint or the table referenced by that constraint. Most RDBMs follow the relational model, you can only create a FK reference to a primary or candidate key in another table (in practical terms an index would already exist). MySQL does not have this restriction and will automatically create an index on the referenced column (or use an existing index if the column(s) referenced comprise(s) the start of that index).

1

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.