MySQL Index Statistics, Index Dives, and Why Estimates Go Wrong

Almost every mystery plan regression I have debugged on MySQL ends at the same place: a row estimate that was wrong, feeding a cost model that was therefore wrong, choosing a join order or index that was therefore wrong. The query did not change. The schema did not change. The statistics drifted, or were never good enough to begin with, and one day the cost of plan A crept past the cost of plan B.

PostgreSQL people arriving at MySQL are often surprised by how differently the two systems build these estimates, and MySQL people are often surprised by how little of it they have ever configured. InnoDB estimates cardinality by sampling a handful of index pages, twenty by default, and a shocking amount of production behavior hangs off that number.

This post is a tour of where MySQL 8.0 and 8.4 row estimates actually come from, the knobs that control them, and which fixes work when the optimizer is guessing wrong.

Where InnoDB cardinality numbers come from

InnoDB does not maintain exact cardinality. For each index it estimates the number of distinct values by sampling leaf pages: with persistent statistics, innodb_stats_persistent_sample_pages of them, default 20, regardless of whether the index has a thousand pages or ten million. The results land in two ordinary tables you can and should query: mysql.innodb_table_stats and mysql.innodb_index_stats. The n_diff_pfx rows are the interesting ones; they record estimated distinct values for each column prefix of each index.

SELECT index_name,
       stat_name,
       stat_value,
       sample_size,
       last_update
FROM mysql.innodb_index_stats
WHERE database_name = 'shop'
  AND table_name = 'orders'
ORDER BY index_name, stat_name;

Two things to internalize from that output. First, last_update tells you how stale the statistics are, which is the first question in any misestimate investigation. Second, sample_size tells you how little data backs the numbers. Twenty pages sampled from a 500 GB table is a survey, not a census, and for columns whose values cluster by insertion order, sequential IDs, timestamps, tenant-sharded data, the sample can be badly unrepresentative.

Persistent statistics and the 10 percent rule

Since 5.6, innodb_stats_persistent is on by default: statistics survive restarts and are recalculated in the background when roughly 10 percent of a table's rows have changed, courtesy of innodb_stats_auto_recalc. The 10 percent trigger is the operational trap. On a ten-billion-row table, a billion rows must change before automatic recalculation fires. A hot recent partition of data can be completely invisible to statistics that were last computed weeks ago. The classic symptom is a query filtering on recent dates that gets a terrible plan because, statistically speaking, those dates do not exist yet.

The knobs are per-table as well as global, which is the right way to use them: crank sampling on the tables where estimates matter and leave the rest alone.

ALTER TABLE orders STATS_SAMPLE_PAGES = 256, STATS_AUTO_RECALC = 1;
ANALYZE TABLE orders;

ANALYZE TABLE with persistent statistics is cheap, it re-samples index pages rather than scanning the table, so scheduling it nightly, or right after bulk loads and big deletes, is usually free insurance. One historical caution: in earlier 8.0 releases, ANALYZE TABLE implicitly flushed the table from the table definition cache, and on systems with long-running queries this could briefly stall new statements against that table. Later 8.0 releases removed that behavior, but if you run an older 8.0 patch level, check before adding ANALYZE TABLE to peak-hours automation.

Index dives, and the cliff at eq_range_index_dive_limit

For an actual query, the optimizer often does better than stored statistics: for a range or equality condition on an indexed column, it performs an index dive, descending the B-tree to estimate how many rows the specific range contains. Dives are accurate and reflect current data, which is why simple range queries usually get decent estimates even with stale table statistics.

But dives cost B-tree descents, so MySQL rations them. When a condition contains more equality ranges than eq_range_index_dive_limit, think IN lists, the optimizer abandons dives for that predicate and falls back to the stored per-index average, the n_diff numbers above. The default limit is 200. This produces one of MySQL's strangest performance cliffs: a query with 150 items in an IN list estimates accurately and picks the right index, and the same query with 250 items switches to stored statistics, misestimates on a skewed column, and picks a different plan entirely. Teams hit this with ORM-generated IN lists whose length depends on user data, which makes the regression look random.

You can raise the limit, and on skewed columns it is often worth it, but recognize the trade: dives add planning cost per query, which matters for high-QPS statements with giant lists. The structural fixes, loading the list into a temporary table and joining, or chunking the list below the limit, remove the cliff instead of moving it.

Histograms: statistics for the columns indexes forget

Everything above concerns indexed columns. Filters on non-indexed columns historically got default selectivity guesses, and skew made those guesses comical: a status column where 95 percent of rows are 'done' and the query wants 'failed'. MySQL 8.0 added histograms for exactly this case.

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

SELECT column_name,
       histogram->>'$."histogram-type"' AS type,
       histogram->>'$."number-of-buckets-specified"' AS buckets
FROM information_schema.column_statistics
WHERE schema_name = 'shop'
  AND table_name = 'orders';

MySQL builds a singleton histogram, exact per-value frequencies, when distinct values fit in the bucket budget, and an equi-height histogram otherwise. Histograms shine on low-to-medium-cardinality skewed columns used in WHERE clauses that do not deserve an index, since a histogram adds no write-path cost, unlike an index. They are ignored for columns where the optimizer can dive instead, so do not bother creating them on well-indexed columns.

The operational catch: histograms do not maintain themselves under the default settings, and a stale histogram can be worse than none, since the optimizer trusts it confidently. If you cannot schedule refreshes after meaningful data shifts, either use the AUTO UPDATE option where your version supports it or skip the histogram. Drop them with ANALYZE TABLE ... DROP HISTOGRAM when a column's distribution stops being stable enough to summarize.

What actually fixes a misestimate

When EXPLAIN ANALYZE shows estimated rows an order of magnitude off actual, and if you have not built that reading habit, the comparison with how PostgreSQL surfaces the same signal in MySQL EXPLAIN vs PostgreSQL EXPLAIN ANALYZE is a good primer, work the list in this order:

• Check last_update in innodb_index_stats. If statistics predate the data shape the query touches, run ANALYZE TABLE and re-plan. Cheapest possible fix.
• Raise STATS_SAMPLE_PAGES on the affected table. If ANALYZE helps briefly and drifts again, the sample is too small for the table's size or clustering. 256 pages on a large hot table is a reasonable starting point.
• Count your equality ranges. If the query has a long IN list, you may be past eq_range_index_dive_limit. Confirm by testing with a short list; fix with a join table, chunking, or a deliberate limit raise.
• Histogram skewed non-indexed filter columns. With a refresh schedule, or not at all.
• Only then reach for hints. FORCE INDEX and JOIN_ORDER treat the symptom and go stale silently. Sometimes necessary, never first.

Misestimates also power MySQL's most famous plan flip, where ORDER BY with LIMIT convinces the optimizer to walk an ordered index instead of using the selective one. Statistics feed that decision too, and I dissected it in ORDER BY LIMIT regressions in MySQL vs PostgreSQL.

Where MonPG fits

Statistics drift is invisible until a plan flips, which is why the durable defense is workload history: per-query-family latency baselines that catch the flip the day it happens, with the deploy-or-drift question answerable from data. MonPG does this for PostgreSQL today, and we are building MySQL monitoring (coming soon) to track the same regression signals for MySQL fleets, including the estimate-versus-actual evidence this post walks through by hand. MonPG does not monitor MySQL yet, and I will not pretend otherwise. If PostgreSQL is also part of your fleet, the platform is live there, and you can start there while MySQL support lands.