Every bad MySQL query plan I have debugged eventually came down to the same root cause: the optimizer guessed wrong about how many rows a predicate would match. Sometimes the guess was off by a factor of a thousand, and a factor of a thousand is the difference between a nested loop on an index and a full table scan with a filesort, between 5 ms and 5 seconds. The optimizer is not stupid; it is working from statistics that do not describe your data. Column histograms, added in MySQL 8.0, are the tool for one specific and very common version of that problem: skewed values on columns the statistics machinery otherwise treats as uniform.
This is how histograms actually work, when they help, and, just as important, when the optimizer ignores them entirely.
How the optimizer estimates rows
For a WHERE clause, the optimizer needs selectivity: what fraction of the table matches each predicate. For indexed columns it can do index dives, probing the B-tree to count rows in a range, which is accurate for small ranges but gets expensive and is deliberately capped; past the cap it falls back to stored index statistics. Those statistics come from ANALYZE TABLE. InnoDB samples a number of random index pages, and with persistent statistics enabled, the default since 5.6 and controlled by innodb_stats_persistent plus innodb_stats_persistent_sample_pages, the samples are stored and reused rather than recomputed on every restart. The default sample is 20 pages, a small sample for a big table, and on skewed data the estimates inherit that skew badly.
For non-indexed columns there is nothing at all: no statistics, no dives, just heuristics. A predicate on an unindexed column historically got a fixed guess, and on skewed data that guess could be wrong by orders of magnitude. That is the hole histograms fill.
Creating and inspecting histograms
You build one explicitly; MySQL never creates histograms on its own:
ANALYZE TABLE orders
UPDATE HISTOGRAM ON status WITH 32 BUCKETS;
SELECT table_name, column_name,
JSON_EXTRACT(histogram, '$."sampling-rate"') AS sampling_rate,
JSON_EXTRACT(histogram, '$."histogram-type"') AS histogram_type,
JSON_LENGTH(histogram, '$.buckets') AS buckets
FROM information_schema.COLUMN_STATISTICS
WHERE schema_name = 'app';
ANALYZE TABLE orders DROP HISTOGRAM ON status;ANALYZE TABLE ... UPDATE HISTOGRAM reads the column data, sampling if the table is large within the bound of histogram_generation_max_mem_size, sorts it, and stores the distribution as JSON in the data dictionary, visible through information_schema.COLUMN_STATISTICS. Two things are worth checking in that JSON. The sampling rate: if the memory limit forced a small sample on a huge table, the histogram inherits the sample's error. And the histogram type, which tells you which of the two shapes MySQL chose.
Singleton vs equi-height
A singleton histogram stores individual values and their cumulative frequencies, one bucket per distinct value; MySQL picks it when the number of distinct values fits in the requested bucket count. For a status column with eight distinct values, singleton is perfect: the optimizer knows the exact fraction of rows with status = 'pending' instead of guessing uniformity. An equi-height histogram divides the value range into buckets of roughly equal row counts and stores per-bucket cumulative frequencies plus summary statistics; it handles high-cardinality columns where singleton would need a bucket per value.
The type matters when you set the bucket count. Asking for 16 buckets on a column with ten thousand distinct values gets you an equi-height histogram where each bucket holds roughly a sixteenth of the rows, narrow value ranges where the data is dense, wide ones where it is sparse, never an even 625 distinct values apiece, and estimation error inside a bucket is averaged away. My rule of thumb: start with the default bucket count the server picks, and raise it only when EXPLAIN still shows bad estimates on the most skewed predicates.
When histograms help, and when they are ignored
The wins are specific. Skewed, non-indexed columns in WHERE clauses are the classic case: a status or type column where 99 percent of rows are 'done' but every query filters 'pending'. With a histogram, the optimizer sees that 'pending' is 0.3 percent of the table and plans accordingly: joins in the right order, the right driving table, no million-row intermediate result. Correlated predicates benefit indirectly, because even one accurate selectivity in a multi-predicate WHERE can fix the overall estimate enough to change the join order.
The ignored cases matter just as much, because people add histograms, see no change, and conclude they do not work. First, indexed columns: when an index exists, the optimizer generally prefers index statistics and dives over the histogram, so a histogram on an indexed column often changes nothing. Second, range predicates on indexed columns go through the index machinery for the same reason. Third, and this one bites, histograms only help when the optimizer can see a constant. A parameterized predicate, a join comparison, or a stored procedure variable gives the optimizer nothing to look up, so the histogram sits unused and the uniformity assumption returns. Fourth, maintenance is its own chore, and the rules shifted in 8.4. Through 8.0 the split is absolute: the histogram clauses manage histograms only, a plain ANALYZE TABLE refreshes index statistics, and one does not substitute for the other. MySQL 8.4 adds an AUTO UPDATE option to the histogram clause; a histogram declared that way is refreshed by a plain ANALYZE TABLE and by the automatic statistics recalculation, while the default MANUAL UPDATE keeps the old split. I stay with manual refreshes on a schedule I control, because a histogram that changes silently under a production workload is a plan-change mystery waiting to happen.
When the optimizer ignores your histogram, the honest fallback is the old toolkit: an index on the column, a generated column to make an expression indexable, a hint for the hot queries, or rewriting the predicate. Histograms are a scalpel for selectivity, not a replacement for indexing.
Statistics hygiene: persistence and sample pages
Histograms sit on top of a statistics pipeline that also needs care. With innodb_stats_persistent on, index statistics survive restarts and are recalculated automatically when roughly a tenth of the table changes under innodb_stats_auto_recalc, or on demand with ANALYZE TABLE. The sample size, innodb_stats_persistent_sample_pages at its default of 20, controls estimate quality; on large, skewed tables I raise it to 100 or more for the tables that matter, accepting a slower ANALYZE in exchange for estimates that stop whipsawing between plans on every auto-recalc. If you have ever watched a plan flip to something terrible for no apparent reason after a batch load, auto-recalc on a small sample is the usual suspect, and the fix is a bigger sample plus a scheduled ANALYZE TABLE after bulk loads.
Keep a record of your histograms. They are schema metadata, as real as indexes: which columns have them, how many buckets, when they were last refreshed. A histogram built on last year's data distribution decays exactly like any other statistic, and an application team that does not know it exists will be very confused the day it starts influencing plans.
Plan drift is a monitoring problem too
A bad estimate rarely announces itself; the plan just flips one morning and latency triples, and no average-based dashboard can tell you why. Closing that gap is the design brief for MonPG's MySQL monitoring, coming soon: row-estimate drift and plan changes surfaced as first-class events with a cause attached, the same way the PostgreSQL product already correlates plan changes with statistics updates on the MonPG platform. The boundary, for honesty's sake: MonPG monitors PostgreSQL today and does not watch MySQL yet. Until that changes, the workflow above needs nothing but a MySQL shell and an hour of honesty: EXPLAIN the bad plan, compare the estimates against reality, histogram the skewed non-indexed columns, and sample harder on the big skewed tables. The rest of this series lives on the blog.