MySQL 8: Histogram Statistics Against Skewed Data Distributions

1. Why classic cardinality estimates fail on skewed columns

For non indexed columns and for evaluating range conditions, the optimizer traditionally relies on a single cardinality value per column, a coarse estimate of the number of distinct values relative to the total row count. From that one value, an average hit rate gets derived for every condition, regardless of which concrete value is actually being filtered on.

That approach works reliably as long as a column's values are reasonably evenly distributed. But once a few values occur extremely often while the vast majority occur rarely, for example an order status where ninety percent of all rows are 'complete' and the rest spread across a dozen rare states, a pure average estimate systematically misleads the planner. A condition on a rare status gets massively overestimated, a condition on the frequent status gets massively underestimated.

2. What execution plan mistakes actually result from this

If the optimizer overestimates the hit count of what is actually a rare condition, it may decide against an existing index and read the entire table instead, because a full scan appears cheaper than an index lookup with row retrieval when the estimated hit count is high. When the actual hit count is low, that plan is noticeably slower than the index access that would have been the right choice.

Conversely, an underestimate in a multi table join order can cause MySQL to process what it believes is the smaller intermediate result set first, even though it is actually the largest, skewing the entire join strategy unfavorably. Such misjudgments often only surface in practice as data volume grows, since small test databases frequently do not reflect the skew of the real distribution at all.

3. How a histogram improves the data foundation

A histogram splits the observed values of a column into several buckets and stores a distinct, differentiated frequency figure for each bucket, instead of using a single average value for the whole column. MySQL supports two variants for this: equi height histograms, where each bucket covers roughly the same share of total rows, and singleton histograms, where each frequent individual value gets its own bucket.

For a column with a few, highly dominant values, MySQL automatically picks the singleton variant, because it delivers the most precise information for exactly that case. A condition on the frequent value 'complete' then gets a realistic, high hit estimate, while a condition on the rare value 'fraud_suspected' gets a correspondingly low estimate, exactly the differentiation a simple cardinality figure cannot provide.

4. Creating histograms in practice with ANALYZE TABLE

Creating a histogram uses the already familiar ANALYZE TABLE statement, extended with the UPDATE HISTOGRAM ON clause followed by the affected column. Optionally the number of buckets can be specified, MySQL defaults to 100 buckets, which already gives a sufficiently fine resolution for most use cases.

The operation reads the table once to determine the distribution, but does not create a lasting lock and only minimally affects ongoing writes. The resulting histogram is stored as a JSON document in the data dictionary and persists until it is explicitly updated or dropped, so it is not subject to any automatic expiry.

-- Create a histogram with 100 buckets for the status column
ANALYZE TABLE sales_order
    UPDATE HISTOGRAM ON status;

-- With an explicit bucket count for a finer resolution
ANALYZE TABLE sales_order
    UPDATE HISTOGRAM ON status WITH 64 BUCKETS;

-- Remove an existing histogram again
ANALYZE TABLE sales_order
    DROP HISTOGRAM ON status;

5. Inspecting and evaluating an existing histogram

The actual bucket data of a created histogram can be queried directly through information_schema.column_statistics, where it is stored as a structured JSON document. That document contains, among other things, the histogram type, the number of buckets, and for each bucket the covered value range and the cumulative share of the total row count.

For a practical assessment it is often enough to compare the singleton values against the actually known top values of a column, for example through a simple GROUP BY query with COUNT. If the shares stored in the histogram diverge noticeably from current reality, for example because the status distribution shifted sharply after a discount campaign, that is a clear signal to rebuild the histogram promptly.

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

6. When histograms actually produce better plans

Histograms deliver the most value on columns that should not or cannot be indexed, for example because they only rarely serve as a filter condition and a permanent index would mean pure write overhead without a matching read benefit. For such columns, the optimizer previously relied entirely on coarse estimates, so a histogram provides the largest relative gain here.

Histograms are equally helpful for range conditions on skewed numeric columns, for example order totals with a few very high outliers, or on foreign key columns with a strongly unequal distribution, such as a store_id where a single store accounts for ninety percent of the data volume. On evenly distributed or already well indexed columns with high selectivity, an additional histogram usually no longer adds a measurable benefit.

7. Practical example: a histogram on the order status column

A Magento installation that has grown to several million orders typically accumulates a strongly skewed distribution in the status field: most orders are long since completed, while states like 'payment_review' or 'holded' make up only a small fraction, but are queried specifically and frequently during day to day operations, for example on a support dashboard or in automated escalation reports.

A histogram on this column ensures that a targeted query for the rare status 'payment_review' is correctly recognized as highly selective, so the optimizer prefers an existing index, while a query for 'complete' continues to be realistically classified as low selectivity and, if that is cheaper overall, deliberately falls back to a table scan.

8. Maintenance and refresh strategy for histograms

Unlike regular index statistics, histograms are not automatically recomputed once a certain volume of changes accumulates, they stay unchanged until explicitly refreshed. For columns with a stable distribution that barely changes, that is unproblematic, but for columns with seasonal or event driven shifts, for example after large sales campaigns, refreshing should become a fixed part of the regular maintenance schedule.

In practice, it works well to tie histogram refreshes to the same rhythm as other maintenance tasks, for example as part of a weekly maintenance window alongside ANALYZE TABLE for index statistics. Since the operation itself is resource friendly and does not create a lengthy lock, it can also be run outside classic maintenance windows without issue whenever a short term shift in the data distribution becomes known.

9. Limits: what histograms do not solve

Histograms are no substitute for a missing index on a genuinely frequently used, highly selective filter condition. They only improve the optimizer's estimation basis, but change nothing about physical access speed when a full table scan remains the only available access method without an index. Anyone hoping to solve a performance problem through histograms alone, without reviewing the underlying index strategy, is usually disappointed.

Additionally, a histogram only captures the distribution within a single column and does not record correlations between several columns. Two columns that are strongly related in practice, for example store_id and shipping country, continue to be evaluated independently by the optimizer, which can still lead to suboptimal plans even with well maintained histograms and may require a functional index column or a rewritten query.

10. Summary

11. FAQ: Histogram Statistics: Key Takeaways