FORCE INDEX mySQL ......我把它放在哪里？

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?

我在几乎没有成功的地方试过它，这让我相信有一些更复杂的东西我不知道了吗？

1 个解决方案

#1

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 BY before GROUP BY to get the latest entry per owner_id, you're using a nonstandard and undocumented behavior of MySQL to do that.

如果您在GROUP BY之前使用ORDER 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-group tag for many explanations of better solutions for this type of query.

搜索最大-n-per-group标记，以获得针对此类查询的更好解决方案的多种解释。

#1