Index on Foreign Key in MYSQL

Question

Is the following add index code redundant after adding the FK?

ALTER TABLE main ADD FOREIGN KEY (language_id) REFERENCES main_language (id);
ALTER TABLE main ADD INDEX (language_id);

Why or why not?

The answer given below is much stronger than any answer provided for the above question (the selected answer is one sentence long). I think we should keep this question on strength of the answer. — David542, Oct 23 '14 at 19:43
There is an old question on this subject: http://stackoverflow.com/questions/304317/does-mysql-index-foreign-key-columns-automatically Other use of the explicit definition of the index, is to choose the index name. Otherwise MySql will use the columns name. — Joao Cunha, Oct 23 '14 at 19:48
Sorry for multiple rapid edits below-- I learned something new myself while linking the docs... — Michael Berkowski, Oct 23 '14 at 19:50
mysql requires all foreign keys to have an index. you used to have create one manually, nowadays it'll do it for you. but you MUST have the foreign keys indexed. — Marc B, Oct 23 '14 at 19:50

Michael Berkowski · Accepted Answer · 2014-10-23T19:48:10.843

Yes, it is redundant. Adding the FOREIGN KEY constraint implicitly creates an index on language_id. If you needed to add a different composite index which incorporated other columns, it would not be redundant but the single column is.

ALTER TABLE main ADD FOREIGN KEY (language_id) REFERENCES main_language (id);

-- This is redundant
ALTER TABLE main ADD INDEX (language_id);

-- This is not redundant
ALTER TABLE main ADD INDEX (other_column, language_id);

According to MySQL docs, MySQL will not create a new index on the FOREIGN KEY column if one is already present at the time the FOREIGN KEY is created.

MySQL requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. Such an index is created on the referencing table automatically if it does not exist. This index might be silently dropped later, if you create another index that can be used to enforce the foreign key constraint. index_name, if given, is used as described previously.

So because the indexed columns are used left-to-right, if you already had this index on the column, creating the FOREIGN KEY constraint would not need to create a new index.

-- Already has a composite index with the FK column listed first
ALTER TABLE main ADD INDEX (language_id, other_column);
-- This won't create a new index when the constraint is defined
ALTER TABLE main ADD FOREIGN KEY (language_id) REFERENCES main_language (id);

Edit: According to this note in the above paragraph:

This index might be silently dropped later, if you create another index that can be used to enforce the foreign key constraint

...the docs seem to imply that if you created your proposed redundant index, MySQL may silently drop the index it created implicitly as part of the FORIEGN KEY constraint since the explicitly created one can be used instead. So it may not continue to maintain both indices.

Likewise, this suggests that adding the composite index above could result in MySQL no longer needing to maintain the FK's implictly created index.

ALTER TABLE main ADD FOREIGN KEY (language_id) REFERENCES main_language (id);
-- Adding this later may allow MySQL to drop the implicit index created with the FK
ALTER TABLE main ADD INDEX (language_id, other_column);

Index on Foreign Key in MYSQL

1 Answers1