MySQL中的外键索引

I have simple code.

我有简单的代码。

CREATE TABLE foo (

client_id int,
order_id int,

PRIMARY KEY (client_id, order_id),
INDEX (order_id),

FOREIGN KEY (client_id) REFERENCES baz(id),
FOREIGN KEY (order_id) REFERENCES bar(id)

);

I know that MySQL will automatic add index to column with primary key, but what if I have complex primary key? (example in my code). Why I must add index to second column in primary key? I think that MySQL will automatic add index only for first column but to second, third ... I must add this constraint manually? Is any answer in official documentation?

我知道MySQL会自动使用主键向列添加索引，但是如果我有复杂的主键怎么办？（在我的代码中的例子）。为什么我必须在主键的第二列添加索引？我认为MySQL会自动为第一列添加索引但是第二列，第三列......我必须手动添加这个约束吗？官方文档中有任何答案吗？

1 个解决方案

#1

you can refer to the link http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html

你可以参考链接http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html

if any table has a multi-column index for eg: (col1, col2, col3), then you can have the search capabilities for (col1), (col1, col2), and (col1, col2, col3)

如果任何表具有例如：（col1，col2，col3）的多列索引，则可以具有（col1），（col1，col2）和（col1，col2，col3）的搜索功能

This index will never be used if the search do not form a Left most prefix on the index, which in this case is col1

如果搜索没有在索引上形成最左边的前缀（在本例中为col1），则永远不会使用此索引

So you need to have col1 as prefix and a search with col2,col3 wont use the index

所以你需要将col1作为前缀并使用col2进行搜索，col3不要使用索引

You might need a different index for the same, so you will have to index col2 separately

您可能需要不同的索引，因此您必须单独索引col2

#1