MySQL中的FORCE INDEX-我在哪里放置它?

问题描述

我有以下运行正常的MySQL查询.除了需要添加 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?

这是原始查询:

$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");

以这种方式构造查询，以便在您怀疑的情况下，我可以在 GROUP BY 之前执行 ORDER BY .

The query is constructed this way so that I can do the ORDER BY before the GROUP BY, in case you're wondering.

我需要添加的是:

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?

推荐答案

index 提示的语法记录在这里:
http://dev.mysql.com/doc/refman/5.6/en/index-hints.html

The syntax for index hints is documented here:
http://dev.mysql.com/doc/refman/5.6/en/index-hints.html

FORCE INDEX 紧接在表引用之后:

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

---

警告:

如果在 GROUP BY 之前使用 ORDER BY 来获取每个 owner_id 的最新条目，则使用的是非标准且未记录的文档MySQL的行为来做到这一点.

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.

不能保证它将在将来的MySQL版本中继续使用，并且该查询可能是任何其他RDBMS中的错误.

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.

搜索 greatest-n-per-group 标签可提供有关此类型查询的更好解决方案的许多说明.

Search the greatest-n-per-group tag for many explanations of better solutions for this type of query.

这篇关于MySQL中的FORCE INDEX-我在哪里放置它?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！