FORCE INDEX in MySQL - where do I put it?

Question

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?

Answer 1

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.

Answer 2

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.

You should be using JOIN_INDEX, GROUP_INDEX, ORDER_INDEX, and INDEX instead, for v8.

From the docs you can learn that these have nuanced meanings allowing much finer-grained control over what you're telling the optimizer compared to the previous features. You can read the full details in the docs, but if all you want is the equivalent of "force index" you can do that by specifying the table and index in a special comment right after SELECT:

SELECT /*+ index(table_name index_name) */ col1, col2 FROM table_name;