database – Does MySQL index foreign key columns automatically?

The Question :

269 people think this question is useful

Does MySQL index foreign key columns automatically?

The Question Comments :

The Answer 1

237 people think this answer is useful

Yes, but only on innodb. Innodb is currently the only shipped table format that has foreign keys implemented.

The Answer 2

136 people think this answer is useful

Apparently an index is created automatically as specified in the link robert has posted.

InnoDB 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 is in contrast to some older versions, in which indexes had to be created explicitly or the creation of foreign key constraints would fail.) index_name, if given, is used as described previously.

InnoDB and FOREIGN KEY Constraints

The Answer 3

21 people think this answer is useful

Yes, see InnoDB and FOREIGN KEY Constraints.

The Answer 4

11 people think this answer is useful

You don’t get the index automatically if you do an ALTER TABLE (instead of CREATE TABLE), at least according to the docs (the link is for 5.1 but it’s the same for 5.5):

[…] When you add a foreign key constraint to a table using ALTER TABLE, remember to create the required indexes first.

The Answer 5

8 people think this answer is useful

For those who are looking for quote from 5.7 docs:

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.

The Answer 6

4 people think this answer is useful

As stated it does for InnoDB. At first I thought it was strange that many other (in particular MS SQL and DB2) doesn’t. TableSpace scans are only better than index scans when there are very few table rows – so for the vast majority of cases a foreign key would want to be indexed. Then it kind of hit me – this doesn’t necessarily mean it has to be a stand alone (one column) index – where it is in MySQL’s automatic FK Index. So, may be that is the reason MS SQL, DB2 (Oracle I’m not sure on) etc leave it up to the DBA; after all multiple indexes on large tables can cause issues with performance and space.

The Answer 7

2 people think this answer is useful

Yes, Innodb provide this. You can put a foreign key name after FOREIGN KEY clause or leave it to let MySQL to create a name for you. MySQL automatically creates an index with the foreign_key_name name.

CONSTRAINT constraint_name
FOREIGN KEY foreign_key_name (columns)
REFERENCES parent_table(columns)
ON DELETE action
ON UPDATE action

The Answer 8

0 people think this answer is useful

Yes, Mysql index foreign key automatically when you create a table that has a foreign key to another one.

The Answer 9

-2 people think this answer is useful

It’s not possible to get index key automatically use

ALTER TABLE (NAME OF THE TABLE) ADD INDEX (FOREIGN KEY)

Name of the table which you have created for example photographs and FOREIGN KEY for example photograph_id. The code should be like this

ALTER TABLE photographs ADD INDEX (photograph_id);

Add a Comment