Does MySQL index foreign key columns automatically?
Add a comment
|
8 Answers
Yes, but only on innodb. Innodb is currently the only shipped table format that has foreign keys implemented.
5 Comments
Robert Gamble
Do you have any reason to believe that MySQL will ever allow foreign keys on non-indexed columns for any other table type?
Grant Limberg
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.
AbiusX
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
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.
Déjà vu
@AbiusX 5.1 is probably too old, see MrAlexander's answer below.
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.
4 Comments
GWed
+1 much better answer than the one selected as provides proof from docs
Courtney Miles
The quoted text doesn't appear to be included in the MySQL docs anymore, making it unclear if this is still true or not.
Lucas Basquerotto
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. 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.
Comments
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.
2 Comments
Ciprian Stoica
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.
BMiner
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.
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.
1 Comment
BMiner
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.
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
Comments
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.
Comments
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);
Comments
