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
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