Is the foreign key automatically indexed in MySQL?

Posted on

Question :

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

Answer :

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.

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).

Leave a Reply

Your email address will not be published. Required fields are marked *