MySQL 如何强制对内部连接表建立索引？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2310062/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to force an index on inner joined tables?
提问by David
How do I force indexes on a query similar to this. I need to force an index on foo and bar individually.
如何在与此类似的查询上强制建立索引。我需要分别在 foo 和 bar 上强制建立索引。
SELECT foo.*, bar.*
FROM foo
INNER JOIN bar ON foo.rel_id = bar.rel_id
WHERE foo.status = 1
AND bar.status = 1
回答by Ηλ?α?
Assuming index aexists on foo and index bon bar:
假设索引a存在于 foo 上，索引b存在于 bar 上：
SELECT foo.*, bar.*
FROM foo FORCE INDEX (a)
INNER JOIN bar FORCE INDEX (b) ON foo.rel_id = bar.rel_id
WHERE foo.status = 1
AND bar.status = 1
would force index selection on MySql
将强制在 MySql 上选择索引
回答by Lieven Keersmaekers
The obvious thing would be to create a covering index on rel_idand statusof both tables to satisfy the join and where requirement.
最明显的事情是将上创建一个覆盖索引rel_id和status两个表来满足连接和地方的要求。
What have you tried so-far?
你都尝试了些什么？
edit
编辑
You provide index hintsbut the crux of everyone answering seems to be that you shouldn't have to do that.
您提供了索引提示，但每个人回答的关键似乎是您不应该这样做。
From MySQL 4.0.9 on, you can also use FORCE INDEX, which acts like USE INDEX (index_list) but with the addition that a table scan is assumed to be very expensive. In other words, a table scan is used only if there is no way to use one of the given indexes to find rows in the table.
从 MySQL 4.0.9 开始，您还可以使用 FORCE INDEX，它的作用类似于 USE INDEX (index_list)，但另外一个表扫描被认为是非常昂贵的。换句话说，只有在无法使用给定索引之一来查找表中的行时才使用表扫描。
