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

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.

MySQL 5.6 has support for a new format of EXPLAIN, which the MySQL Workbench GUI can visualize in a more appealing way. But that doesn't help you if you're stuck on MySQL 5.5 or earlier.

MySQL does have hints as @AirThomas mentions, but you should really use them sparingly. In a simple query like the one you show, it should never be necessary to use index hints -- if you have the right index. And using index hints means you have hard-coded index names into your application, so if you add or drop indexes, you have to update your code.

In your query, an index on (post_date, post_status, post_type, post_id) is not going to help.

You want the left-most column in the index to be used for row restriction. So put post_status, post_type first. Best if the more selective column is first. That is, if post_type = 'post' matches 3% of the table, and post_status = 'publish' matches 1% of the table, then put post_status first before post_type.

Since you used = for both conditions and the AND operator, you know that all matching rows are basically tied with respect to those two columns. So if you use post_date as the third column in the index, then the optimizer knows it can fetch the rows in the order they are stored in the index, and it can skip doing any other work for the ORDER BY. You can see this working if "Using filesort" disappears from your EXPLAIN output.

So your index likely should be:

ALTER TABLE wp_posts ADD INDEX (post_status, post_type, post_date);

You may also enjoy my presentation How to Design Indexes, Really.

You don't need to add ID to the index in this case, because InnoDB indexes automatically contain the primary key column(s).

LIMIT 18310, 5 is bound to be costly. MySQL has to generate the whole result set on the server side, up to 18315 rows, only to discard most of them. Who in the world needs to skip to the 3662^nd page, anyway?!

SQL_CALC_FOUND_ROWS is a major performance killer when you have large result sets that you're paging through, because MySQL has to generate the whole result set, both before and after the page you requested. Best to get rid of that query modifier unless you really need FOUND_ROWS(), and even if you do need the number of rows, it can sometimes* be quicker to run two queries, one with SELECT COUNT(*).
(* Test both ways to make sure.)

Here are some more tips on optimizing LIMIT:

• http://www.mysqlperformanceblog.com/2006/09/01/order-by-limit-performance-optimization/
• http://www.mysqlperformanceblog.com/2008/09/24/four-ways-to-optimize-paginated-displays/
• http://dev.mysql.com/doc/refman/5.6/en/limit-optimization.html

Try changing the order of your index definition to

post_type, post_status, post_date, post_id

or

post_date desc, post_type, post_status,  post_id