How do you force a query in MySQL to use an index?

Question

I am trying to improve the performance of a hammered wordpress DB by adding indexes to queries that appear in the slow query log.

In MS SQL you can use query hints to force a query to use an index but it is usually quite easy to get a query to use an index if you cover the columns correctly etc.

I have this query that appears in the slow query log a lot

SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID 
FROM wp_posts  
WHERE 1=1  
  AND wp_posts.post_type = 'post' 
  AND (wp_posts.post_status = 'publish')  
ORDER BY wp_posts.post_date DESC 
  LIMIT 18310, 5;

I created a covering unique index on wp_posts on post_date, post_status, post_type and post_id and restarted MySQL however when I run explain the index used is

status_password_id

and in the possible keys my new index doesn't even appear although it's a covering index e.g I just get

type_status_date,status_password_id

Therefore neither the used index or the possible choices the "optimiser" if MySQL has one is even considering my index which has post_date as the first column. I would have thought a query that is basically doing a TOP and ordering by date with

ORDER BY wp_posts.post_date DESC LIMIT 18310, 5;

Would want to use an index sorted by date for speed, especially one that had all the other fields required to satisfy the query in it as well?

Does MySQL have query hints to force an index to be used for speed/performance tests or is there something else I need to do to see why this index is being ignored.

I would love it if Navicat had a Visual Query Execution Plan like MS SQL but it seems EXPLAIN is the best it has to offer.

Anyone with any hints on how I can either force the index to be used or work out why its being ignored would be very helpful!

Thanks

Answer 1

Does MySQL have query hints to force an index to be used for speed/performance tests or is there something else I need to do to see why this index is being ignored.

The documentation answers this question in some detail:

By specifying USE INDEX(index_list), you can tell MySQL to use only one of the named indexes to find rows in the table. The alternative syntax IGNORE INDEX(index_list) can be used to tell MySQL to not use some particular index or indexes. These hints are useful if EXPLAIN shows that MySQL is using the wrong index from the list of possible indexes.

You can also use FORCE INDEX, which acts like USE INDEX(index_list) but with the addition that a table scan is assumed to be very expensive. In other words, a table scan is used only if there is no way to use one of the given indexes to find rows in the table.

Each hint requires the names of indexes, not the names of columns. The name of a PRIMARY KEY is PRIMARY. To see the index names for a table, use SHOW INDEX.

If USE INDEX doesn't work, try using IGNORE INDEX to see what the optimizer's second choice is (or third, and so on).

A simple example of the syntax would be:

SELECT * FROM t1 USE INDEX (i1) IGNORE INDEX (i2) WHERE ...

There are many more where that came from, in the linked docs. I've linked to the version 5.0 pages, but you can easily navigate to the appropriate version using the left sidebar; some additional syntax options are available as of version 5.1.