Force MySQL to use my indexThere are times when nothing works right. Sometimes you get in the office and the coffee machine is out of coffee. Sometimes you get out of bed and you come to realize it's monday. But sometimes it's even worse. Sometimes you have your MySQL database not utilizing your indexes. You are sure you've added the correct indexes but yet they are not being used. Somtimes you are just out of luck.
MySQL relies on its MySQL Query Optimizer to generate a plan to execute the query. The plan says which table is to be queried first and which second (if the query involves more than one table) and which indexes to be used in the process. Sometimes the optimizer doesn't do a good job and needs some help. First thing you should check is the EXPLAIN command. It can tell you exactly what strategy or plan of action was chosen for your specific query. Observe the key, possible_keys, and rows columns of the EXPLAIN command result. They'll tell you which key is being used for each table part of your query. Which keys were candidates for use in your query. And how many rows were read to satisfy your query. Let's take the query below for example:
FROM posts p
WHERE p.public = 1 AND p.approved = 1
ORDER BY p.views
Check composite indexesA composite index is an index based on two or more columns. You should check if there's a composite index on all the columns in your WHERE and ORDER clauses. Check if a composite index exists for
(id, views, approved, public)
Check the order of columns in composite indexesSometimes having a composite index on all the columns is not enough. Keep in mind that the order of columns inside the composite index matters a lot. If nothing works for you try creating a new composite index that has the columns inside the composite index reordered. (id, views, approved, public)
(id, approved, public, views)
Be more specific in your ORDER BY clauseOften there's a big difference between saying
ORDER BY views
ORDER BY views, id
You are making a real hint to MySQL Query Optimizer about what you really want as a result. This can be the difference between having a full table scan and using the correct index. Especially if you have more than one composite indexes with different set of columns available in the table.
Be more specific in your WHERE clauseYou can try adding some more conditions in your WHERE clause. Even if they don't narrow down the search results they can hint the query optimizer to use the correct index.
Try adding id > 0 in the WHERE clause. At first glance it seems like it won't do anything. After all IDs are always greater than 0. What it does however is hint to the MySQL Query Optimizer to use an index that has the id column in it.
WHERE public = 1 AND approved = 1 AND id > 0
Check your tablesTry running table ANALYZE TABLE or OPTIMIZE TABLE. If the table schema was altered recently running those commands might fix your issue.
Use FORCE INDEXTry using the index hints as specified in Mysql Documentation.
Using USE INDEX tells MySQL to use only one index.
Using IGNORE INDEX tells MySQL to not use a specific index.
Using FORCE INDEX tells MySQL to use a specific index and avoid using full table scan if at all possible.