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?
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.
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?
This question is related to
mysql
indexing
greatest-n-per-group
The syntax for index hints is documented here:
http://dev.mysql.com/doc/refman/5.6/en/index-hints.html
FORCE INDEX goes 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.
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.
Search the greatest-n-per-group tag for many explanations of better solutions for this type of query.
FORCE_INDEX is going to be deprecated after MySQL 8:
Thus, you should expect USE INDEX, FORCE INDEX, and IGNORE INDEX to be deprecated in
a future release of MySQL, and at some time thereafter to be removed altogether.
https://dev.mysql.com/doc/refman/8.0/en/index-hints.html
You should be using JOIN_INDEX, GROUP_INDEX, ORDER_INDEX, and INDEX instead, for v8.
Source: Stackoverflow.com