FORCE INDEX mySQL ...我把它放在哪里？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19061873/
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
FORCE INDEX mySQL ...where do I put it?
提问by user2643870
I have the following mySQL query that works perfectly fine. Except that I need to add a "FORCE INDEX" and I'm unsure on where I have to do this. I tried just about every location and always receive a mySQL error. What am I doing wrong?
我有以下 mySQL 查询可以正常工作。除了我需要添加一个“FORCE INDEX”并且我不确定我必须在哪里这样做。我几乎尝试了每个位置，但总是收到 mySQL 错误。我究竟做错了什么？
Here is the original query:
这是原始查询：
$sql_select_recent_items = $db->query("SELECT * FROM (SELECT owner_id, product_id, start_time, price, currency, name, closed, active, approved, deleted, creation_in_progress FROM db_products ORDER BY start_time DESC) as resultstable
WHERE resultstable.closed=0 AND resultstable.active=1 AND resultstable.approved=1 AND resultstable.deleted=0 AND resultstable.creation_in_progress=0
GROUP BY resultstable.owner_id
ORDER BY start_time DESC");
The query is constructed this way so that I can do the "ORDER BY" before the "GROUP BY", in case you're wondering.
查询是以这种方式构造的，以便我可以在“GROUP BY”之前执行“ORDER BY”，以防万一。
What I need to add is:
我需要补充的是：
FORCE INDEX (products_start_time)
I tried it just about everywhere without success, which leads me to believe that there's something more complex that I'm missing?
我几乎在任何地方都尝试过但没有成功，这让我相信我遗漏了一些更复杂的东西？
回答by Bill Karwin
The syntax for index hints is documented here:
http://dev.mysql.com/doc/refman/5.6/en/index-hints.html
索引提示的语法记录在此处：http:
//dev.mysql.com/doc/refman/5.6/en/index-hints.html
Index index go right after the table reference:
索引索引紧跟在表引用之后：
SELECT * FROM (
SELECT owner_id, product_id, start_time, price, currency, name, closed,
active, approved, deleted, creation_in_progress FROM db_products
FORCE INDEX (products_start_time)
ORDER BY start_time DESC) as resultstable
WHERE resultstable.closed=0
AND resultstable.active=1
AND resultstable.approved=1
AND resultstable.deleted=0
AND resultstable.creation_in_progress=0
GROUP BY resultstable.owner_id
ORDER BY start_time DESC
WARNING:
警告：
If you're using ORDER BYbefore GROUP BYto get the latest entry per owner_id, you're using a nonstandard and undocumented behavior of MySQL to do that.
如果您ORDER BY之前使用GROUP BY来获取每个 owner_id 的最新条目，那么您正在使用 MySQL 的非标准和未记录的行为来做到这一点。
There's no guarantee that it'll continue to work in future versions of MySQL, and the query is likely to be an error in any other RDBMS.
不能保证它会在 MySQL 的未来版本中继续工作，并且查询很可能在任何其他 RDBMS 中出错。
Search the greatest-n-per-grouptag for many explanations of better solutions for this type of query.
搜索最大 n-per-group标签以获取有关此类查询的更好解决方案的许多解释。
