index_not_being_used_reasons_checklist.txt (1072B)
Q: You notice a query that’s performing slowly. The table involved has several million rows, and the execution plan shows it isn’t using an index even though the column in the WHERE clause has an index. What are some possible reasons the optimizer might not be using the index, and how would you go about investigating or fixing it? A: There can be a lot of reasons, it can be low cardinality, mismatched data types on a JOIN (e.g. VARCHAR in t1, INT in t2), a query contains an OR (that often kills the optimizer's ability to work with indexes), some function applied to a where clause, like WHERE length(isin) <- unindexable, sometimes the optimizer just does weird stuff, I've seen buggy behaviour with IN ()'s performing better when rewritten to multiple selects using WHERE ='s. I'd check with SHOW CREATE TABLE on affected tables if there's a JOIN involved, and check the query with EXPLAIN in any case. In some situations I might also check into htop to see if I see a CPU being maxed out on the long term, that usually indicates a big optimization misfire.