Re: Indexing FOREIGN KEY

[Bill Karwin]

The rule of thumb is that a foreign key requires an index.

The function of a foreign key to enforce referential integrity would be so slow that it would be unusable, if indexes did not exist on both the foreign key and the primary key it references.

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

"In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. Starting with MySQL 4.1.2, such an index is created on the referencing table automatically if it does not exist."

Further down the page...

"InnoDB requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. Starting with MySQL 4.1.2, the index on the foreign key is created automatically. In older versions, the indexes must be created explicitly or the creation of foreign key constraints fails."

Regards,
Bill K.