Why MySQL Optimizer Picks the Wrong Index and How to Fix It

When interviewers ask why the MySQL optimizer chooses the wrong index, the answer isn’t simply “index not used.” The optimizer decides based on a cost model and table statistics such as index cardinality, page count, data distribution, and sampled rows.

If the statistics are stale or the cost model doesn’t match the real workload, the optimizer may pick a sub‑optimal index.

Two common reasons cause inaccurate statistics:

Massive inserts or updates leave the statistics outdated. After a bulk update, a column’s selectivity can drop, yet the optimizer still prefers the old index.

MySQL samples only a limited number of data pages instead of scanning the whole table. This random sampling can miss skewed distributions, leading the optimizer to favor a slower index.

To address these issues you have two main strategies:

Run ANALYZE TABLE to recompute statistics and increase the number of sampled pages (e.g., from the default 20 to 50 or 100).

When statistics remain unreliable, use the FORCE INDEX hint to tell the optimizer to prioritize a specific index, overriding its automatic choice.

While FORCE INDEX can improve query performance when the optimizer’s choice is wrong, it doesn’t guarantee optimal execution if the underlying data distribution is still mismatched.

By understanding the optimizer’s decision process and applying these techniques, you can confidently explain and resolve index‑selection problems in interviews.