Should i specifying INDEXES on fields which are foreign keys?
I have a MySQL database with about 10 tables, and about 6 of them are linked to each other in some way or another开发者_运维技巧. MY question is, should i be specifying INDEXES on the foreign keys?
Thanks
from the docs: "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. "
source: http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html
Yes you should, if cardinality is high enough. Well, just mentioned that it's MySQL-specific question. Seems so that you have an index created automatically when not present with MySQL.
Gryphius's answer is more adequate then.
精彩评论