✏️ Explanatory Question
Level: Very Hard — Tests deep optimizer knowledge; there are many reasons, and "cost-based decision" is only one.
orders(status) exactly matching your WHERE status = 'completed'. But EXPLAIN still shows type=ALL, key=NULL — a full table scan. The index is definitely there (SHOW INDEX confirms it). Give me the possible reasons.
orders table (10M rows)| status value | row count | % of table |
|---|---|---|
| 'completed' | 9,500,000 | 95% |
| 'pending' | 400,000 | 4% |
| 'cancelled' | 100,000 | 1% |
The main cause here: 'completed' matches 95% of the table. The optimizer calculates that using the index (random I/O for 9.5M lookups + reading the rows) is more expensive than just scanning the whole table sequentially. So it correctly chooses a full scan. Low selectivity = index ignored.
| # | Reason | Fix |
|---|---|---|
| 1 | Low selectivity — condition matches a large % of rows | Expected behaviour; scan is genuinely cheaper |
| 2 | Function on the column — WHERE YEAR(created_at)=... |
Rewrite as a sargable range |
| 3 | Implicit type conversion — WHERE phone = 123 (phone is VARCHAR) |
Match types: phone = '123' |
| 4 | Leading wildcard — LIKE '%text' |
Use 'text%' or a FULLTEXT index |
| 5 | Stale statistics — optimizer has outdated cardinality | ANALYZE TABLE |
| 6 | OR across different columns / not a leftmost prefix of a composite index | Rewrite with UNION or reorder index |
-- REASON 1: low selectivity -> optimizer skips index (correctly)
EXPLAIN SELECT * FROM orders WHERE status = 'completed'; -- ALL (95% match)
EXPLAIN SELECT * FROM orders WHERE status = 'cancelled'; -- ref (1% match, uses index)
-- REASON 2: function kills the index
-- BAD:
SELECT * FROM orders WHERE YEAR(created_at) = 2026;
-- GOOD:
SELECT * FROM orders WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01';
-- REASON 3: implicit conversion (phone is VARCHAR)
SELECT * FROM users WHERE phone = 9876543210; -- BAD: no index, per-row cast
SELECT * FROM users WHERE phone = '9876543210'; -- GOOD: uses index
-- REASON 5: stale stats -> optimizer misjudges; refresh them
ANALYZE TABLE orders;
-- FORCING an index (use sparingly, as a last resort / to test)
SELECT * FROM orders FORCE INDEX (idx_status)
WHERE status = 'completed';FORCE INDEX: It's a diagnostic tool, not a permanent fix. If the optimizer avoids an index, it usually has a good reason. Forcing it can make things slower. Use it to test a hypothesis, then address the real cause.