does mysql index foreign key columns automatically?

Question

The subject says it all...

Answer 1

Yes, see http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html.

Answer 2

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

Answer 3

Apparently an index is created automatically as specified in the address 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."

http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html.

Answer 4

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.