如何提示要在 MySQL 选择查询中使用的索引？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11731822/
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 hint the index to use in a MySQL select query?
提问by frequent
I have a MySQL query (running MySQL 5.0.88), which I'm trying to speed up. The underlying table has multiple indices and for the query in question, the wrong index is used (i_active- 16.000 rows, vs. i_iln- 7 rows).
我有一个 MySQL 查询（运行 MySQL 5.0.88），我正在尝试加快速度。基础表有多个索引，对于有问题的查询，使用了错误的索引（i_active- 16.000 行，对i_iln- 7 行）。
I'm not very experienced with MySQL but read there is a use indexhint, which can force mySQL to use a certain index. I'm trying it like this:
我对 MySQL 不是很有经验，但读到有一个use index提示，它可以强制 mySQL 使用某个索引。我正在尝试这样：
SELECT art.firma USE INDEX (i_iln)
...
but this produces a MySQL error.
但这会产生 MySQL 错误。
Question:
Can anyone tell me what I'm doing wrong? (Except running 5.0.88, which I can't change.)
问题：
谁能告诉我我做错了什么？（除了运行 5.0.88，我无法更改。）
回答by Raptor
You missed the
你错过了
FROM table
Correct SQL should be:
正确的 SQL 应该是：
SELECT art.firma FROM your_table USE INDEX (i_iln) WHERE ....
回答by Markus Mikkolainen
select * from table use index (idx);
回答by MikA
sometimes, with use index (index_name)optimizer might go for table scan, if you use hint force index, optimizer will be forced to use index, will go for table scan only if no ways left to get the rows with provided index.
有时，使用索引（index_name）优化器可能会进行表扫描，如果使用提示强制索引，优化器将被迫使用索引，只有在没有办法获取具有提供索引的行时才会进行表扫描。
SELECT art.firma FROM art FORCE INDEX (i_iln);
for more detail on hints USE INDEXand FORCE INDEXcheck this link
有关提示USE INDEX和FORCE INDEX 的更多详细信息，请查看此链接
回答by kuldeep upadhyay
Select Coloumn1,Coloumn2,Coloumn.... FROMTABLE_NAMEUSE INDEX(index_name) WHEREColoumn="condition";
选择 Coloumn1,Coloumn2,Coloumn.... FROM TABLE_NAME USE INDEX( index_name) WHEREColoumn="condition";
if you have correct index thn you dnt need to use index(). your query automic select correct index.If your query slow after using index thn recheck your index ,something wrong in index. thanks in advance.enter code here
如果您有正确的索引，则不需要使用 index()。您的查询会自动选择正确的索引。如果您在使用索引后查询变慢，请重新检查您的索引，说明索引有问题。提前致谢。请在此处输入代码
