Skip to content 
MySQL Histograms provide the optimizer with real distribution data so that it can correctly estimate selectivities and generate faster query plans—often even without an additional index. I’ll show you how I set up and monitor histograms in MySQL 8+ using ANALYZE TABLE, and how I use them to make better decisions regarding joins, filters, and scans.
Key points
Short Focus: The following bullet points outline what I pay particular attention to when using histograms.
- Selectivity Instead of gut feelings: more realistic cardinality estimates
- Without an index Faster: Better plan selection for skewed distributions
- types Understanding: Using Singleton vs. Equi-Height Strategically
- Buckets Taxes: Weigh the write-off against metadata costs
- Care At a Glance: Update, Check, and Delete if Necessary
Why Histograms Without an Index Are Effective
I use Histograms, because otherwise the optimizer often assumes a uniform distribution and consequently selects poor plans. A histogram represents the Distribution of Values It approximates a column and thus provides realistic selectivity estimates for predicates such as =, >, BETWEEN, IN, or IS NULL. The optimizer then decides whether an index range scan, a table scan, or a join strategy using nested loops is more efficient. For example, if a condition matches only 0.1 % of the rows, I prefer a targeted access rather than a broad scan. If, on the other hand, a filter matches almost all rows, I avoid expensive index lookups that offer no benefit, thereby increasing the Efficiency every plan.
Histogram Types in MySQL 8.0
I distinguish between two types: Singleton and Equi-Height. Singleton histograms group frequently occurring individual values into separate buckets—ideal for columns with a few dominant categories such as „active,“ „inactive,“ or „archived.“ Equi-Height histograms divide the range of values so that each bucket contains a similar number of Lines ; this is suitable for continuous or skewed distributions such as prices, timestamps, or „gapped“ ID ranges. Both variants provide the optimizer with more accurate hit rates for filters. I always choose the type based on data characteristics, not personal preference.
Technical Fundamentals: Controlling Data Type Selection in MySQL
MySQL determines the specific Histogram Variation automatically based on the data distribution. In practice, this means: If the number of distinct values (NDV) is small enough relative to the number of buckets, the result is effectively a singleton histogram; otherwise, an equi-height histogram is generated. I therefore „choose“ the type indirect, by specifying the appropriate column and a suitable number of buckets. For columns with very few but highly dominant categories, I deliberately set a small number of buckets to achieve singleton-like precision for these values. For finely distributed, continuous data, I increase the number of buckets incrementally until EXPLAIN returns the desired Selectivity reflects.
Important: Histograms are single-column. You cannot directly represent dependencies between columns (e.g., status and country). In such cases, it helps to create a histogram for the most selective column and adjust the join order accordingly.
Choosing the Right Buckets
MySQL uses 100 by default Buckets, but allows 1 to 1024 using WITH N BUCKETS. More buckets increase resolution, but also increase metadata and the effort required for analysis. I usually start conservatively, measure the effect on EXPLAIN, and increase the number gradually if the plan still seems unsuitable. For highly concentrated values (e.g., 90 % in one status), a few buckets are often sufficient; for finely distributed prices or timestamps, more buckets are worthwhile. The goal is a reasonable Granularity, which has significantly reduced misjudgments without unnecessarily increasing the administrative burden.
Practical Example: Workflow with ANALYZE TABLE
I follow a clear Workflow: First, I identify columns that frequently appear in WHERE or JOIN clauses and show clearly skewed distributions. Then I generate a histogram using `ANALYZE TABLE tbl UPDATE HISTOGRAM ON col WITH N BUCKETS;` and check it via `INFORMATION_SCHEMA.COLUMN_STATISTICS`. After data moves, I update the statistics again with `ANALYZE TABLE`. If a statistic is not appropriate, I remove it with `ANALYZE TABLE tbl DROP HISTOGRAM ON col;`. To evaluate the plan quality, I read Interpreting EXPLAIN ANALYZE and compare estimates with actual figures Lines from.
Specific Orders and Control
I follow a reproducible process consisting of a few clear steps and verify the generated JSON statistics.
-- Create histograms on individual columns
ANALYZE TABLE orders UPDATE HISTOGRAM ON status WITH 32 BUCKETS;
ANALYZE TABLE orders UPDATE HISTOGRAM ON created_at WITH 128 BUCKETS;
-- Multiple columns in a single run with the same number of buckets
ANALYZE TABLE orders UPDATE HISTOGRAM ON status, payment_method WITH 64 BUCKETS;
-- Deleting specific histograms
ANALYZE TABLE orders DROP HISTOGRAM ON status;
-- Visual inspection of the statistics
SELECT
SCHEMA_NAME, TABLE_NAME, COLUMN_NAME,
JSON_PRETTY(HISTOGRAM) AS histogram
FROM INFORMATION_SCHEMA.COLUMN_STATISTICS
WHERE SCHEMA_NAME = DATABASE()
AND TABLE_NAME = 'orders'
AND COLUMN_NAME IN ('status','created_at');
I evaluate the impact immediately using EXPLAIN ANALYZE:
EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE status = 'canceled'
AND created_at >= NOW() - INTERVAL 7 DAY;
Does the estimate improve? rows If there is a noticeable change—for example, if the plan switches from a full scan to an index-range scan or changes the join order—the adjustment was successful. If the deviation remains large, I increase or decrease the number of buckets and compare the results again.
Example: Order Status and Rare Values
In an „orders“ table, the „completed“ status often dominates, while „pending“ is moderately common and "canceled" is very rare; this Imbalance Without a histogram, this can easily lead to incorrect selectivities. If an API queries for „canceled,“ the optimizer may incorrectly choose a full table scan even though a narrow index access would suffice. With a singleton histogram, MySQL recognizes that „canceled“ accounts for only a tiny fraction of the data and switches to an index range scan or optimizes the join order. This reduces latency, and I don’t need an additional index for every Variant of a filter. In dashboards with strict SLOs, this adjustment often results in noticeable performance improvements.
Time Series and Timestamps
With time series, there are many Accesses based on fresh data; older time windows usually remain inactive. An equi-height histogram on `created_at` or `updated_at` distinguishes heavily trafficked time periods from rarely used ones. The optimizer then correctly assesses whether a range scan is appropriate or whether a table scan would achieve the goal more quickly. Especially with partial time filters applied to large tables, I notice significant changes in execution plans and lower I/O costs. I consider the Statistics Updates are more frequent here because the focus shifts with day-to-day operations.
Partitions, Data Types, and Collations
I examine the data distribution on partitioned tables across all partitions. Significant variations (e.g., by month) can smooth out global histograms. If individual partitions are extremely selective or extremely broad, I also test using partition-pruning filters in the WHERE clause to see if the execution plan quality is still acceptable. Overall, I make sure to formulate filters in such a way that MySQL can identify partitions early on exclude can.
Histograms work best with scalar, comparable data types (numbers, date/time values, VARCHAR/CHAR with the appropriate collation). For LOB/JSON Data I tend to rely on Generated Columns with extracted, typed values, and, if necessary, supplement them with histograms or indices. For strings, the Collation The comparison logic; depending on the collation, values may match (e.g., case-sensitive). I keep the collation consistent with the queries to obtain realistic selectivities.
Limits and Missteps
Histograms are particularly useful for estimating individual columns with Constants Good; however, they can only represent multi-column dependencies to a limited extent. They reach their limits with highly correlated columns or dynamic parameters (e.g., those populated by the application). Boolean fields or columns with a nearly uniform distribution rarely benefit from additional statistics. On the other hand, too many buckets and excessive maintenance can increase administrative and analysis time. I therefore use histograms selectively and regularly check the Effect based on actual designs.
Optimizer Check and Update
I check the Use from histograms via ANALYZE TABLE and relevant optimizer options, so that the planner can make effective use of the statistics. In high-traffic systems, I schedule updates during quiet time windows or in batches after large data loads. Before and after, I compare EXPLAIN and EXPLAIN ANALYZE outputs to evaluate changes in join sequences, filter steps, and cost models. If there are negative effects, I react immediately and roll back a statistic. For further control of the Optimizer Options I make sure that dependencies on other statistics do not go unnoticed and cause incorrect Assumptions produce.
Monitoring, Regression Protection, and Playbook
I'm building a lightweight Playbook For production use:
- Set a baseline: Before making any changes, run EXPLAIN ANALYZE to record the execution time, „rows examined,“ and handler count.
- Create/modify histogram: focus on the filter columns, conservative buckets.
- Measure immediately afterward: plan, estimated vs. actual lines; a deviation factor of >10 is a red flag for me.
- Fine-tuning: Move buckets up or down; if necessary, adjust the filter order in the query.
- Have a rollback ready: DROP HISTOGRAM if latency increases.
- Automation: Run ANALYZE during maintenance windows after ETL loads or large DML batches.
To analyze the causes, I use Optimizer Traces and EXPLAIN ANALYZE to determine whether the optimizer is bringing the correct selective table „to the front“ based on the histograms. For A/B testing, I experimentally fix the join order (STRAIGHT_JOIN) or enable/disable individual indexes to evaluate the effect of the statistics in isolation.
From an organizational standpoint, a short one works well Change-Log For each table: column, number of buckets, time, and before/after measurement values. This makes it easier to make corrections later and prevents ambiguous interactions.
Operational Aspects: Blocking, Costs, Portability
ANALYZE TABLE performs a Metadata Lock on the table, but it does not permanently block normal read/write operations. For very large tables, I allow for enough time; histogram generation uses sampling and is memory-limited (keyword: internal working memory for the calculation). The space required for the statistics themselves remains moderate: a few dozen to a few hundred kilobytes per column with 100–256 buckets is a realistic estimate. Still, I calculate the total, because many columns multiplied by many tables result in visible metadata.
At Logical Dumps (mysqldump) does not include histograms in the data; after a restore, I deliberately recreate them. They are preserved during an in-place upgrade. On the server side, I need sufficient privileges for `ANALYZE TABLE` on the respective objects; in strictly regulated environments, I integrate this maintenance into maintenance pipelines.
When Histograms Are Useless
I'll skip Histograms for columns that have very few values and can be estimated accurately anyway. Even where a good index already covers minimal sets of results, a histogram rarely provides any additional benefit. Uniform distributions do not require complex, fine-grained analysis. In highly dynamic, write-intensive systems, maintenance can create unnecessary load if I trigger it too frequently. In such situations, I use the Energy I'd rather focus on index strategies, query design, and caching.
Cheat Sheet in Table Format
I use the following Overview For quick decisions: Which histogram type is appropriate, how do I set buckets, and what are the associated costs? The table serves as a reference guide when reviewing problematic queries. I update it based on insights gained from EXPLAIN ANALYZE and production metrics. In doing so, I keep in mind that data distributions change and historical assumptions become outdated. The key is to Plan Quality to confirm this with actual measurements.
| Aspect | Recommendation | Benefit | trade-off | Example |
|---|---|---|---|---|
| Type | Singleton with a few dominant values | Exact hit rates for common categories | Not very helpful for continuous areas | order_status |
| Type | Equi-Height for Skewed, Continuous Data | Better estimation across the range of values | More metadata for many buckets | created_at, price |
| Buckets | Start at 100, then adjust | Balanced resolution | Higher processing and memory load at 512–1024 | WITH 100 BUCKETS |
| Care | After making major changes to the data, run ANALYZE | Current Selectivities | Schedule a maintenance window | ANALYZE TABLE … UPDATE HISTOGRAM |
| Control | Check using COLUMN_STATISTICS | Transparency and Auditing | JSON parsing required | INFORMATION_SCHEMA.COLUMN_STATISTICS |
How it fits into the overall tuning picture
I treat Histograms as a building block alongside indexes, query design, caching, and hardware parameters. Often, a good histogram reorders joins, reduces I/O, and ensures consistent response times. Nevertheless, it does not replace sound indexing strategies or an efficient schema. Those who take a closer look at query planning decisions will benefit from Understanding Execution Plans and compares cost models with actual durations. I regularly check whether the Workloads whether they still align with the statistics or if adjustments are needed.
Advanced Join Scenarios
Histograms are particularly useful when multiple tables with filters are involved. Example:
SELECT o.id, o.amount
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.country = 'DE'
AND o.status = 'canceled'
AND o.created_at >= NOW() - INTERVAL 30 DAY;
Without histograms, the optimizer may underestimate the selectivity of o.status=’canceled‘ or overestimate the proportion of German users. With a histogram on u.country and status not specified (if applicable, also on o.created_at) the planner usually recognizes that the combination is extremely selective. In practice, I then see that MySQL first determines the smaller subset (e.g., via an index on users(country) or orders(status, created_at)) and only then performs the join—instead of scanning the large table. This saves on I/O, buffer usage, and CPU resources, and stabilizes latency even under load.
Because histograms only single-column Index strategies remain important: A composite index on (status, created_at) can further speed up the range scan. The histogram primarily ensures that the optimizer uses this Strategy considers to be inexpensive at all.
Summary for practice
I set MySQL-I use histograms when the optimizer gets it wrong using default statistics and skewed distributions produce incorrect execution plans. With `ANALYZE TABLE`, I build, update, and remove statistics specifically on the columns that dominate filters and joins. I choose between Singleton and Equi-Height based on the data, and I calibrate the number of buckets using performance measurements. I use EXPLAIN ANALYZE to verify that join orders, filter positions, and scans change as intended. This way, I achieve significant improvements with minimal Overhead Noticeably faster queries—often without the need for additional indexes.
