311

Does MySQL index foreign key columns automatically?

Micha Wiedenmann
  • 19,979
  • 21
  • 92
  • 137
Dónal
  • 185,044
  • 174
  • 569
  • 824

8 Answers8

279

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

Nae
  • 14,209
  • 7
  • 52
  • 79
Grant Limberg
  • 20,913
  • 11
  • 63
  • 84
  • 1
    Do you have any reason to believe that MySQL will ever allow foreign keys on non-indexed columns for any other table type? – Robert Gamble Nov 20 '08 at 04:38
  • I really couldn't answer that. You may want to look up the Maria and Falcon storage engines that are to be released in MySQL 6.0 and see if they support foreign keys on non-indexed columns. – Grant Limberg Nov 20 '08 at 06:08
  • 1
    Apparently this is not true. I have a large table (1 million records) and count(*) where fkey=? would take 15 seconds. Added an index on the fkey column, and things go under a second now. – AbiusX Feb 01 '16 at 23:14
  • Same experiment with another table and 10 million records. This is ofc MySQL 5.1 InnoDB. The table has three fields, one is primary key integer, the other is already indexed. The third was a foreign key to primary key of another table. Without adding an explicit index, lookups took several seconds here. Show index from table also didn't show an index on it. – AbiusX Feb 01 '16 at 23:19
  • @AbiusX 5.1 is probably too old, see MrAlexander's answer below. – Déjà vu Feb 04 '19 at 09:49
153

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

Justin Johnson
  • 30,978
  • 7
  • 65
  • 89
  • 11
    +1 much better answer than the one selected as provides proof from docs – GWed Jan 31 '14 at 11:42
  • 6
    The quoted text doesn't appear to be included in the MySQL docs anymore, making it unclear if this is still true or not. – Courtney Miles Jul 17 '14 at 02:39
  • 7
    @user2045006 you can refer to [doc 5.0](http://dev.mysql.com/doc/refman/5.0/en/create-table-foreign-keys.html) as well [doc 5.6](http://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html) for the exact quoted text – sactiw Mar 17 '15 at 12:23
  • 2
    In the current docs, there is just a slight change in the text (I assume the meaning is similar): `InnoDB permits a foreign key to reference any index column or group of columns. However, in the referenced table, there must be an index where the referenced columns are the first columns in the same order. ` – Lucas Basquerotto Sep 16 '19 at 14:15
13

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.

Fahmi
  • 2,607
  • 1
  • 20
  • 15
11

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.

Thomas Lundström
  • 1,589
  • 1
  • 13
  • 18
  • 5
    I also tried on MySQL 5.6 and MariaDB 10 and ALTER TABLE crated an index. It's interesting that mysqlindexcheck reported that index as being a "redundant index". I tried to drop it but I got the following error: "ERROR 1553 (HY000): Cannot drop index 'index_name': needed in a foreign key constraint". So, it's not possible to drop that index and keep the foreign key. – Ciprian Stoica Oct 20 '15 at 10:11
  • 2
    You may want to revise your answer. MySQL *always* creates an index to speed up foreign key checks *if one does not already exist*. The docs are trying to tell you that creating indexes before foreign key constraints may speed things up a bit. For example, a composite key index that could serve as an index for the foreign key checks could be used by InnoDB instead of auto-generating a redundant index. – BMiner Jan 22 '19 at 15:07
5

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.

Wolf5370
  • 1,374
  • 11
  • 12
  • 2
    You bring up a good point about composite key indexes; however, MySQL will automatically/silently drop an auto-generated single key index if a newly created composite key index fulfills the obligation of making the foreign key checks fast. To be honest, I have no idea why MS SQL, DB2, and others don't do this. They have little excuse. I cannot think of a use case where an auto-generated index on foreign keys would be harmful. – BMiner Jan 22 '19 at 15:04
3

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
BlitZ
  • 12,038
  • 3
  • 49
  • 68
Navrattan Yadav
  • 1,993
  • 1
  • 17
  • 22
0

Yes, MySQL will automatically create an index for the foreign key column when you are defining a foreign key constraint. And if you have already have an index defined over a foreign key columns, MySQL will not create a new index. Instead of that, it will use an existing index that to enforce a foreign key constraint.

danronmoon
  • 3,814
  • 5
  • 34
  • 56
Alekhya Reddy
  • 1
-2

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);
Ghasem
  • 14,455
  • 21
  • 138
  • 171
Ali Raza
  • 1