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- gt query  quot 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 quot     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

User · Answer

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

User · Answer

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

[mysql] FORCE INDEX in MySQL - where do I put it?

Examples related to mysql

Examples related to indexing

Examples related to greatest-n-per-group