When the optimizer picks the wrong index: statistics, histograms, and hints

A specific kind of ticket arrives every few weeks: the query was fast for months, nobody deployed anything, and now it takes four seconds. The index it should use still exists. EXPLAIN shows the optimizer choosing a different one, or a full table scan.

This is a plan-choice problem, not an indexing problem, and the two need different fixes. Adding another index to a table whose statistics are wrong usually produces a third index the optimizer also ignores.

What the optimizer is actually doing

MySQL's optimizer is cost-based. For each candidate access path it estimates how many rows it will examine and multiplies by a cost constant, then picks the cheapest total. It does not measure; it estimates, from statistics that are sampled and approximate.

You can see the arithmetic. EXPLAIN FORMAT=JSON exposes the numbers EXPLAIN hides:

EXPLAIN FORMAT=JSON
SELECT id, total FROM orders
WHERE status = 'pending' AND created_at >= '2026-01-01';

Read three fields per table: rows_examined_per_scan, filtered, and cost_info.read_cost. A plan goes wrong when rows_examined_per_scan is far from reality — the optimizer is not being stupid, it is answering correctly from bad inputs.

To see how bad, run EXPLAIN ANALYZE on 8.0.18 and later. It executes the query and prints estimated and actual rows side by side:

-> Index lookup on orders using idx_status (status='pending')
   (cost=1204 rows=11842) (actual time=0.041..38.2 rows=412891 loops=1)

Estimated 11,842, actual 412,891. That gap of 35x is the whole bug. Find it before you change anything; it tells you which of the fixes below applies.

Cause 1: stale index statistics

InnoDB estimates index cardinality by sampling a number of random index pages, controlled by innodb_stats_persistent_sample_pages (default 20). Twenty pages is a small sample of a 200 GB table. Statistics are recalculated automatically when roughly 10% of rows have changed, which means a table that grows steadily in one direction — an append-only events table, a queue whose status column skews further toward done every day — carries estimates describing a distribution it no longer has.

First move, and it is cheap:

ANALYZE TABLE orders;

The cost: ANALYZE TABLE itself is quick, but on 8.0 it invalidates the table's cached plans and briefly needs a metadata lock, so a long-running query on that table will queue behind it. Run it the way you run any DDL-adjacent operation — with a short lock_wait_timeout and an eye on information_schema.innodb_trx. It also replicates, so it runs on your replicas too.

If re-analyzing fixes the plan and the plan degrades again in three weeks, the sample is too small for the table. Raise it per table rather than globally:

ALTER TABLE orders STATS_SAMPLE_PAGES=200;
ANALYZE TABLE orders;

Higher sampling makes ANALYZE TABLE slower and more I/O-heavy; 200 pages on a large table is still seconds, not minutes, and it is the durable fix for one or two problem tables. Setting it globally for every table on the instance is not.

Cause 2: skew that cardinality cannot express

Index statistics record how many distinct values a column has, not how the rows distribute across them. A status column with 6 distinct values over 10 million rows gives an average of 1.67 million rows per value, so the optimizer estimates that for every status. In reality done holds 9.9 million rows and pending holds 900. The estimate is catastrophically wrong in both directions, and no amount of ANALYZE TABLE helps, because the average is correct.

This is what histograms are for. Since 8.0:

ANALYZE TABLE orders UPDATE HISTOGRAM ON status, country_code WITH 64 BUCKETS;

The optimizer then knows pending is rare and will reach for the index. Worth knowing before you rely on them:

• Histograms are not maintained automatically. They are a point-in-time snapshot and go stale silently. Re-run the statement on a schedule — weekly is usually enough — and treat it as an operational task with an owner.
• They help most on non-indexed columns used as filters, where they improve filtered percentages and join ordering. For an indexed column, a range dive often already gives a good estimate.
• Building one scans the table (sampled on large tables, within histogram_generation_max_mem_size), so build them in a quiet window.
• Inspect them in information_schema.COLUMN_STATISTICS, and drop with ANALYZE TABLE t DROP HISTOGRAM ON col.

Good candidates: low-cardinality status and type columns, soft-delete flags, tenant IDs in a multi-tenant schema where a few tenants hold most of the rows.

Cause 3: the plan is right and the query is wrong

Before reaching for hints, check the honest third possibility. The optimizer avoids your index because using it would be genuinely expensive:

• The predicate is not sargable. WHERE DATE(created_at) = '2026-03-01' cannot use an index on created_at; WHERE created_at >= '2026-03-01' AND created_at < '2026-03-02' can. Same for WHERE amount / 100 > 50 and leading-wildcard LIKE '%foo'.
• Types or collations do not match. Comparing a VARCHAR column to an integer literal, or joining utf8mb4_general_ci to utf8mb4_0900_ai_ci, forces a conversion that disables index use on that side of the join. This is one of the most common causes we find, and it never shows up as an error.
• The index would need a lookup per row. A secondary index that matches 30% of the table plus a random primary-key lookup for each match is slower than a sequential scan. The optimizer is correct here. The fix is a covering index, not a hint.

EXPLAIN showing Using index versus Using where tells you which situation you are in.

Cause 4: the range limit and the join order

Two estimator ceilings worth knowing because they produce sudden, confusing plan flips:

• eq_range_index_dive_limit (default 200) — for IN (...) lists longer than this, MySQL stops diving into the index for precise estimates and falls back to the coarse statistics. A query with 199 IDs in the list plans differently from one with 201. If a generated IN list crosses that boundary at some traffic level, that is your mystery.
• optimizer_search_depth — on queries joining many tables the optimizer greedily prunes the search space and can settle on a poor join order. Joining fewer tables per query is a better answer than tuning this.

Forcing the plan, in the right order

When the statistics are correct and the plan is still wrong, override it. Prefer the least brittle option available:

1. Optimizer hints (8.0, comment syntax) are scoped to one statement and name the behaviour you want:

SELECT /*+ INDEX(orders idx_status_created) */ id, total
FROM orders WHERE status = 'pending' AND created_at >= '2026-01-01';

JOIN_ORDER, NO_MERGE, SEMIJOIN, and SET_VAR are in the same family. Hints are ignored, not fatal, if the named index disappears later — which makes them safer in code than FORCE INDEX.

2. FORCE INDEX is blunter and older: the query fails if the index is dropped or renamed. That failure mode has taken sites down during otherwise routine index cleanup. Use it when hints are not available on your version.

3. Optimizer switches (SET optimizer_switch='...') — reach for these per-session only, for diagnosis. Disabling something globally, such as index condition pushdown, to fix one query trades one slow query for an unknown number of new ones.

Every override is a permanent assumption about data distribution, written into code that outlives the person who wrote it. Leave a comment naming the date and the reason. Re-check hinted queries after any major version upgrade: 8.4 and the releases after it changed cost estimation in ways that make some old hints actively harmful.

A working order of operations

1. EXPLAIN ANALYZE the query. Compare estimated rows against actual rows.
2. If the gap is large and the column is indexed: ANALYZE TABLE. Re-check. If it returns, raise STATS_SAMPLE_PAGES for that table.
3. If the gap is large and the column is skewed: add a histogram, and schedule its refresh.
4. If the estimates are close and the plan is still slow: the optimizer is right. Fix sargability, collations, or add a covering index.
5. Only then hint — with a comment and a review date.

This sequence matters because each step leaves the system in a better state than the one after it. A histogram helps every query touching that column; a FORCE INDEX helps one query and quietly constrains every future schema change.

If you have a plan that flips under load and the estimates look fine in staging, the difference is usually data distribution rather than configuration. Send us the EXPLAIN FORMAT=JSON output, the table definition, and the row counts, and we can generally name the cause before touching the server.