CODEWITHSUNDEEP
mysql
David542
David542

Reputation: 110093

Index on Foreign Key in MYSQL

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?

Upvotes: 2

Views: 2924

Answers (1)

Michael Berkowski
Michael Berkowski

Reputation: 270609

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

Upvotes: 9

Related Questions