A join on MySQL 8.4 runs in seconds one day and minutes the next, and EXPLAIN shows the planner reading the wrong table first. The filter that should make one side tiny, such as status = 'refunded', is on a column with no index. With no index and no histogram, the optimizer has no idea how rare that value is, so the filtered column shows a fixed guess and the join order follows the guess. Histograms fix this without the write cost of an index. ANALYZE TABLE ... UPDATE HISTOGRAM stores the column's value distribution in the data dictionary, and the optimizer uses it to estimate how many rows a predicate keeps. This guide covers when a histogram helps and when an index is the better fix, how to build one, how to see its effect in EXPLAIN and EXPLAIN ANALYZE, and how to keep it current, including the AUTO UPDATE option added in MySQL 8.4.0.
- A histogram tells the optimizer how values are distributed in one column. It changes row estimates and join order, not the access method, so it helps most on non-indexed columns used in
WHEREfilters. - Build one with
ANALYZE TABLE t UPDATE HISTOGRAM ON col WITH N BUCKETS. N runs from 1 to 1024 and defaults to 100. MySQL builds a singleton histogram if the column has N or fewer distinct values, and equi-height otherwise. - Check the result in
INFORMATION_SCHEMA.COLUMN_STATISTICSand in thefilteredcolumn ofEXPLAIN.EXPLAIN ANALYZEshows whether the new estimate matches the real row count. - Histograms go stale. From MySQL 8.4.0,
AUTO UPDATErefreshes a histogram wheneverANALYZE TABLEruns on the table and when InnoDB recalculates persistent statistics.MANUAL UPDATEis still the default. - If the column also drives row lookups, or the predicate can use the range optimizer, build an index instead. The optimizer prefers range estimates to histogram estimates.
Scope and review date: November 2026. This post covers MySQL 8.4 LTS and MySQL 9.7 LTS. The histogram sections of the ANALYZE TABLE and optimizer statistics pages read the same in the 8.4, 9.7, and 26.7 reference manuals. Commands, defaults, and limits were checked against MySQL's documentation on 9 November 2026. They were not run in a lab, so try them on a staging copy of your data first.
What does a MySQL histogram tell the optimizer?
A histogram records how often each value, or each range of values, appears in one column. MySQL stores it in the column_statistics data dictionary table, which persists across restarts and is exposed read-only as INFORMATION_SCHEMA.COLUMN_STATISTICS. The optimizer statistics page says the optimizer uses it to estimate the filtering effect of a column compared with a constant.
The qualifying predicates are =, <>, !=, >, <, >=, <=, IS NULL, IS NOT NULL, BETWEEN, NOT BETWEEN, IN, and NOT IN. The constant can be a constant function such as ABS(-34). A join condition such as o.customer_id = c.id compares two columns, so a histogram does not estimate it directly.
-- The status filter can use a histogram on orders.status.
-- The join condition cannot; it compares two columns.
SELECT o.id, o.total_amount, c.email
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'refunded'
AND c.region = 'EU';Interpretation: both WHERE conditions compare a column with a constant, so both can have a histogram. The planner multiplies each table's estimated rows by its filtering estimate, and the smaller result usually goes first in the join order.
Without a histogram or an index, the optimizer has nothing to measure, so it falls back to constants in the server source. In the MySQL 8.4 source (sql/item.h) they are 0.1 for an equality, 0.3333 for an inequality, and 0.1111 for BETWEEN. A value that matches 0.4% of rows gets the same 10% estimate as one that matches 60%.
Where a histogram enters the plan
Follow the numbers; the dashed path is the fixed guess used when no statistics exist
What changes: only the estimate in step 3. The table is still read the same way, so a histogram moves the join order, not the scan type.
When should you build a histogram instead of an index?
The MySQL manual frames it as a trade-off. An index is updated on every insert, update, and delete. A histogram is built only when you ask for it, so it costs nothing on writes, but it drifts out of date as the data changes.
Two rules decide most cases. First, the optimizer prefers range optimizer estimates to histogram estimates: if a predicate can use an index range, the histogram is ignored. Second, for equality on an indexed column, index dives can give better estimates than a histogram. A histogram on a well-indexed column is often dead weight.
| Situation | Better fix | Why |
|---|---|---|
Low-cardinality, skewed column used only as a filter (status, country_code, is_deleted) | Histogram | An index would rarely be chosen for access, but the estimate decides the join order |
Column that also locates rows (customer_id, created_at ranges) | Index | The index serves the lookup, and the range optimizer supplies the estimate |
| Write-heavy table where another index would hurt | Histogram | No maintenance cost on DML |
| Column covered by a single-column unique index | Neither | MySQL refuses to build the histogram; the index already implies one row per value |
JSON or spatial column, encrypted table, or TEMPORARY table | Index or generated column | Histograms are not supported there |
-- Is the column already the leading column of an index?
SELECT INDEX_NAME, SEQ_IN_INDEX, NON_UNIQUE
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'shop'
AND TABLE_NAME = 'orders'
AND COLUMN_NAME = 'status';Interpretation: no rows means the column is in no index, which makes it a histogram candidate. SEQ_IN_INDEX = 1 means an index can serve range estimates, so a histogram adds little. A hit on a later position only helps when the leading columns are also in the predicate.
Histograms work on stored and virtual generated columns, so a value extracted from JSON into a generated column can have one.
How do you find columns that need a histogram?
Start from queries, not columns. The candidates are statements that scan a table with a selective filter and then join, where the plan is wrong because an estimate is wrong. The sys.statements_with_full_table_scans view lists normalized statements that did full scans.
SELECT db, exec_count, total_latency,
no_index_used_pct, rows_examined_avg, rows_sent_avg,
LEFT(query, 120) AS query
FROM sys.statements_with_full_table_scans
WHERE db = 'shop'
ORDER BY total_latency DESC
LIMIT 10;Interpretation: look for statements with a join and a large gap between rows_examined_avg and rows_sent_avg. Take each one to EXPLAIN. A table whose filtered value is exactly 10.00, 33.33, or 11.11 on a non-indexed predicate is running on the fixed guess.
Then measure the skew. A histogram only matters if the real fraction is far from the guess.
SELECT status,
COUNT(*) AS n,
ROUND(100 * COUNT(*) / SUM(COUNT(*)) OVER (), 2) AS pct
FROM orders
GROUP BY status
ORDER BY n DESC;Interpretation: if refunded is 0.4% of rows and the query filters on it, the 10% guess is 25 times too high. Run this on a replica or a staging copy, because it reads the whole table.
How do you create a histogram with ANALYZE TABLE?
The ANALYZE TABLE reference gives the syntax. One table per statement, one or more columns, and an optional bucket count from 1 to 1024. Without WITH N BUCKETS, MySQL uses 100.
ANALYZE TABLE orders
UPDATE HISTOGRAM ON status, country_code
WITH 64 BUCKETS;+-------------+-----------+----------+-------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+-------------+-----------+----------+-------------------------------------------------------+
| shop.orders | histogram | status | Histogram statistics created for column 'country_code'. |
| shop.orders | histogram | status | Histogram statistics created for column 'status'. |
+-------------+-----------+----------+-------------------------------------------------------+Interpretation: the Msg_text wording is from the manual; the layout is illustrative. If one named column does not exist or has an unsupported type, MySQL still builds the others and reports the failure in its own row, so read every row.
Singleton or equi-height: how many buckets?
You do not pick the type. MySQL builds a singleton histogram, one bucket per distinct value with an exact cumulative frequency, when the column has no more distinct values than the bucket count. Otherwise it builds an equi-height histogram, where each bucket holds a value range, a cumulative frequency, and a distinct-value count.
- For status codes, country codes, and flags, set the bucket count at or above the distinct count, so you get a singleton histogram with exact frequencies.
- For wide numeric or date columns, more buckets give finer ranges. 1024 is the ceiling, and a larger histogram is more data for the optimizer to read when it plans the query.
-- How many distinct values? Pick N at or above this for a singleton.
SELECT COUNT(DISTINCT country_code) FROM orders;Interpretation: if this returns 180, WITH 200 BUCKETS gives a singleton histogram. WITH 64 BUCKETS gives an equi-height histogram that groups rare countries into shared ranges.
During the analysis, InnoDB and MyISAM tables hold a read lock. The statement is written to the binary log by default, so replicas build their own histogram from their own data. Add NO_WRITE_TO_BINLOG (or LOCAL) to keep it on one server.
How do sampling and histogram_generation_max_mem_size affect accuracy?
MySQL reads column values into memory to build the histogram. If the estimated data exceeds histogram_generation_max_mem_size, it samples instead. The system variable reference gives a default of 20000000 bytes and a minimum of 1000000. It has global and session scope and can be changed at runtime. Setting the session value needs privileges for restricted session variables.
Sampling is page-level SYSTEM sampling spread evenly over the table. Since MySQL 8.0.19, InnoDB has its own sampling implementation that avoids a full table scan. The trade-off is accuracy: a sample can miss a rare value entirely, and that rare value is often the one your query filters on.
-- Raise the limit for this session only, then rebuild
SET SESSION histogram_generation_max_mem_size = 200000000;
ANALYZE TABLE orders UPDATE HISTOGRAM ON total_amount WITH 256 BUCKETS;Interpretation: afterwards, check sampling-rate in COLUMN_STATISTICS. A value of 1 means every row was read. A value such as 0.05 means about 5% of the data was read, so small categories may be missing.
-- The sampling counters are disabled by default
SET GLOBAL innodb_monitor_enable = 'sampled_pages_read';
SET GLOBAL innodb_monitor_enable = 'sampled_pages_skipped';
SELECT NAME, COUNT
FROM information_schema.INNODB_METRICS
WHERE NAME IN ('sampled_pages_read', 'sampled_pages_skipped');Interpretation: the manual defines the sampling rate as sampled_pages_read / (sampled_pages_read + sampled_pages_skipped). Use the counters when you want to watch how much InnoDB reads during a large build.
How do you read information_schema.COLUMN_STATISTICS?
Each row holds one column's histogram as a JSON object. The keys you will use most are histogram-type, sampling-rate, last-updated (UTC), number-of-buckets-specified, null-values, and buckets. Histograms built on 8.4 or later also carry auto-update. You only see rows for columns you have some privilege on.
Histogram inventory for one table
Illustrative output: check the type, the sampling rate, and the age of each histogram
Two warnings in three rows: total_amount was built from about 4% of the data and is nearly three months old. Those are the two reasons a histogram stops helping. The JSON keys are listed on the optimizer statistics page.
To see what the optimizer will read for a specific value, pull the buckets.
SELECT HISTOGRAM->>'$."auto-update"' AS auto_update,
HISTOGRAM->>'$."null-values"' AS null_fraction,
JSON_PRETTY(HISTOGRAM->'$.buckets') AS buckets
FROM information_schema.COLUMN_STATISTICS
WHERE SCHEMA_NAME = 'shop'
AND TABLE_NAME = 'orders'
AND COLUMN_NAME = 'status';Interpretation: in a singleton histogram each bucket is [value, cumulative_frequency]. A value's own share is its cumulative frequency minus the previous bucket's. String values come back in the form base64:typeN:..., where N is a type code. Decode the part after the second colon with FROM_BASE64(): cmVmdW5kZWQ= is refunded.
How do you see the histogram's effect in EXPLAIN and EXPLAIN ANALYZE?
In EXPLAIN, the rows column is the estimate for the chosen access method, and filtered is the percentage of those rows the remaining conditions keep. The condition filtering page calls rows × filtered the prefix row count: the rows passed to the next table in the join. The manual's own example is a predicate that histograms show 57% of rows match, so EXPLAIN shows 57.00 with no index on the column.
EXPLAIN for the orders and customers join, before and after a histogram on orders.status
Illustrative numbers for a 2,000,000-row orders table where 0.4% of rows are refunded
No histogram
| EXPLAIN detail | No histogram |
|---|---|
orders rows (table scan) | 2,000,000 |
orders filtered for status = 'refunded' | 10.00 (fixed guess) |
| Estimated orders rows passed on | 200,000 |
| First table in the join | customers, then a lookup into orders per customer |
| Where the estimate came from | Equality constant 0.1 in the server source |
Histogram on status
| EXPLAIN detail | Histogram on status |
|---|---|
orders rows (table scan) | 2,000,000 |
orders filtered for status = 'refunded' | 0.40 |
| Estimated orders rows passed on | 8,000 |
| First table in the join | orders, then a primary key lookup into customers |
| Where the estimate came from | Singleton bucket frequency for refunded |
Same scan, different plan: the rows estimate does not move, because the histogram changes only filtered. The 25-fold drop in prefix rows is what lets the planner start from orders.
EXPLAIN shows what the optimizer believes. EXPLAIN ANALYZE runs the query and prints each iterator's estimate next to what actually happened, always in TREE format.
EXPLAIN ANALYZE
SELECT o.id, o.total_amount, c.email
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'refunded' AND c.region = 'EU'\G
-- Illustrative output, in the format the MySQL 8.4 manual shows
-> Nested loop inner join (cost=... rows=800) (actual time=... rows=1544 loops=1)
-> Filter: (o.status = 'refunded') (cost=... rows=8000)
(actual time=... rows=7912 loops=1)
-> Table scan on o (cost=... rows=2e+6) (actual time=... rows=2e+6 loops=1)
-> Filter: (c.region = 'EU') (cost=... rows=0.1)
(actual time=... rows=0.195 loops=7912)
-> Single-row index lookup on c using PRIMARY (id = o.customer_id) ...Interpretation: compare rows= in the estimate with rows= in the actual part for each Filter line. Close numbers mean the histogram is doing its job. A gap of 10 times or more on a filtered column means the histogram is missing, sampled too thinly, or stale. Large counts print in engineering format, so 2e+6 is two million. EXPLAIN ANALYZE executes the statement, so run it on a replica for anything expensive.
Capture EXPLAIN ANALYZE before you build the histogram, build it on staging, and capture it again. If the join order and the timing do not improve, drop the histogram. An unused histogram is one more statistic to keep fresh.
Should you use AUTO UPDATE or MANUAL UPDATE?
MySQL 8.4.0, released on 30 April 2024, added automatic histogram updates. The 8.4.0 release notes say a histogram created with AUTO UPDATE is rebuilt whenever ANALYZE TABLE runs on its table, and also when InnoDB automatically recalculates persistent statistics. The rebuild reuses the bucket count the histogram was created with. MySQL 8.0 does not have the option.
ANALYZE TABLE orders
UPDATE HISTOGRAM ON status
WITH 16 BUCKETS AUTO UPDATE;
-- Later, a plain ANALYZE TABLE also refreshes the histogram on status
ANALYZE TABLE orders;Interpretation: the second statement updates index statistics and every AUTO UPDATE histogram on the table. MANUAL UPDATE histograms are left alone. Histograms that existed before an upgrade to 8.4 are treated as MANUAL UPDATE, so they do not start refreshing on their own.
The background refresh depends on innodb_stats_auto_recalc. The persistent statistics page says it is on by default and recalculates a table's statistics after more than 10% of its rows change. The per-table STATS_AUTO_RECALC option overrides it.
SELECT @@GLOBAL.innodb_stats_auto_recalc;
SELECT CREATE_OPTIONS
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'shop' AND TABLE_NAME = 'orders';Interpretation: stats_auto_recalc=0 in CREATE_OPTIONS means this table never recalculates in the background, so an AUTO UPDATE histogram there refreshes only when someone runs ANALYZE TABLE.
Use AUTO UPDATE for columns whose distribution moves with the data, such as status columns on a busy orders table. Keep MANUAL UPDATE on very large tables, where a background rebuild of a 1024-bucket histogram is extra read work you would rather schedule yourself, and for histograms you set by hand.
How do you keep histograms fresh, and when do you drop them?
A MANUAL UPDATE histogram describes the table on the day it was built. The manual's advice for a histogram that seems to hurt a query is to regenerate it with ANALYZE TABLE and run the query again. Some DDL also removes histograms without warning:
ALTER TABLEthat drops or modifies a column removes that column's histogram.ALTER TABLE ... CONVERT TO CHARACTER SETremoves histograms on character columns.DROP TABLEandDROP DATABASEremove them;RENAME TABLEkeeps them under the new name.
-- Histograms older than 30 days (last-updated is UTC)
SELECT SCHEMA_NAME, TABLE_NAME, COLUMN_NAME,
HISTOGRAM->>'$."last-updated"' AS last_updated
FROM information_schema.COLUMN_STATISTICS
WHERE CAST(HISTOGRAM->>'$."last-updated"' AS DATETIME(6))
< UTC_TIMESTAMP() - INTERVAL 30 DAY
ORDER BY last_updated;Interpretation: every row is a histogram the optimizer still trusts but nobody has refreshed in a month. Rebuild the ones on tables that changed, and check that histograms you built before a schema change still exist.
To remove a histogram, use DROP HISTOGRAM. To test a plan without histograms but keep them, turn off condition_fanout_filter for one session. The manual warns that this also disables other condition filtering.
ANALYZE TABLE orders DROP HISTOGRAM ON total_amount;
-- Session-only experiment: plan without condition filtering
SET SESSION optimizer_switch = 'condition_fanout_filter=off';Interpretation: DROP HISTOGRAM returns Histogram statistics removed for column 'total_amount'. and leaves the table's other histograms in place.
Since MySQL 8.0.31, UPDATE HISTOGRAM ON col USING DATA 'json' loads a histogram from the same JSON that COLUMN_STATISTICS shows. Save the JSON before a risky change, and you can put back a known-good histogram without rebuilding it. It works on one column per statement.
Histogram runbook for one slow join
Five steps; the only lock is the read lock ANALYZE TABLE takes while it builds
-
Find the candidate query
Look for joins that scan with a selective filter. A filtered value of 10.00, 33.33, or 11.11 on a non-indexed predicate is the fixed guess.
SELECT query, rows_examined_avg, rows_sent_avg FROM sys.statements_with_full_table_scans ORDER BY total_latency DESC LIMIT 10; -
Confirm skew and no usable index
Measure the real fraction on a replica, and check information_schema.STATISTICS for an index that leads with the column.
SELECT status, COUNT(*) FROM orders GROUP BY status; -
Build the histogram on staging
Set the bucket count at or above the distinct count for a singleton histogram. Check that sampling-rate is 1 for small columns.
ANALYZE TABLE orders UPDATE HISTOGRAM ON status WITH 16 BUCKETS; -
Verify the plan
Compare estimated and actual rows on each Filter line, and the join order, against the capture from before.
EXPLAIN ANALYZE SELECT ... \G -
Ship it and schedule the refresh
On 8.4 and later, rebuild with AUTO UPDATE; on 8.0, run ANALYZE TABLE from a scheduled job. Add the 30-day staleness query to monitoring.
ANALYZE TABLE orders UPDATE HISTOGRAM ON status WITH 16 BUCKETS AUTO UPDATE;
Rollback point: any step. ANALYZE TABLE orders DROP HISTOGRAM ON status returns the optimizer to its previous estimates, with no data or schema change to undo.
Frequently asked questions
Does a MySQL histogram replace an index?
No. A histogram only improves row estimates, which changes join order and plan choice. It never lets MySQL find rows faster, because there is no structure to search. If the column is used to locate rows, or a range on it should be read through an index, build the index. Histograms suit filter columns that an index would rarely serve.
Which MySQL version added automatic histogram updates?
MySQL 8.4.0, released on 30 April 2024. The AUTO UPDATE clause of ANALYZE TABLE ... UPDATE HISTOGRAM makes a histogram refresh on every ANALYZE TABLE for its table and on InnoDB's automatic statistics recalculation. MANUAL UPDATE is the default, and histograms created before an upgrade to 8.4 stay manual.
How many buckets should a MySQL histogram have?
For low-cardinality columns, use at least as many buckets as distinct values, so MySQL builds a singleton histogram with exact frequencies. For wide numeric or date columns, start at the default of 100 and raise it toward the 1024 maximum if EXPLAIN ANALYZE still shows large estimate errors for narrow ranges.
Why does EXPLAIN still show filtered 10.00 after I built a histogram?
Check three things. The predicate must compare the column with a constant. If the range optimizer can use an index for that predicate, MySQL prefers its estimate and ignores the histogram. And the build may have failed for that column, so confirm a row exists in INFORMATION_SCHEMA.COLUMN_STATISTICS and read every row of the ANALYZE TABLE output.
Do histograms replicate to MySQL replicas?
The statement does. ANALYZE TABLE is written to the binary log by default, so each replica runs it and builds a histogram from its own copy of the data. Add NO_WRITE_TO_BINLOG or LOCAL to build it on one server only, for example to test it on a replica first.
Key takeaways
- Histograms change the optimizer's filtering estimate, and through it the join order. They do not change how a table is read.
- Use them on non-indexed, skewed columns compared with constants. Use an index when the column locates rows or the range optimizer can serve the predicate.
- Pick the bucket count deliberately, at or above the distinct count for a singleton histogram, and check
sampling-rateafter every build. - Prove the change with
EXPLAINandEXPLAIN ANALYZEbefore and after, on staging or a replica. - On MySQL 8.4 and later, use
AUTO UPDATEfor columns that drift. Monitorlast-updated, and rebuild histograms after column-altering DDL.
Tune MySQL query plans with JusDB
Bad estimates are a common reason a query that was fast last month picks a different plan today. The fix is often a statistic, not a new index, but deciding which one needs EXPLAIN ANALYZE evidence from your own data.
JusDB's MySQL performance tuning team can review your slowest joins, find where row estimates and actual rows diverge, and plan the right mix of indexes, histograms, and refresh schedules, measured against a baseline before anything reaches production.