Chapter 09 · Optimizer, EXPLAIN, Statistics, and Query Plan Engineering
Histograms, Persistent Statistics, Cardinality Errors, and ANALYZE TABLE
Diagnose bad estimates rather than guessing: understand InnoDB persistent index statistics, column histograms, skew, ANALYZE TABLE, and how improved statistics may change estimates or plans without being a universal performance cure.
Learning outcomes
The optimizer cannot know the future, and it does not count every table value before planning every query. It relies on statistics. When those statistics poorly describe a skewed distribution, a logically valid query can receive a poor cardinality estimate. The correct response is to identify the estimate error, understand which statistics feed it, and improve the information only where evidence justifies doing so.
Distinguish persistent InnoDB index statistics from column histograms and explain what each describes.
Identify a skew-driven cardinality error by comparing optimizer estimates with EXPLAIN ANALYZE actual rows.
Use ANALYZE TABLE to refresh index statistics and UPDATE/DROP HISTOGRAM to manage column histograms safely.
Inspect INFORMATION_SCHEMA.COLUMN_STATISTICS and relevant InnoDB statistics metadata without treating sampled values as exact counts.
Explain why ANALYZE TABLE can improve estimates yet leave the same plan—or even reveal that the root problem is missing access-path design.
Two different kinds of optimizer knowledge
InnoDB maintains optimizer statistics about indexes, including estimated cardinality of key prefixes. With persistent statistics enabled, those estimates survive restarts and can be recalculated automatically or by ANALYZE TABLE. A histogram is different: it describes the distribution of values in a table column, especially useful when no index provides suitable distribution information for that predicate.
| Statistics source | Describes | Typical inspection/management |
|---|---|---|
| Persistent InnoDB index statistics | Table/index size and key-distribution estimates used for index planning | SHOW INDEX; INFORMATION_SCHEMA.INNODB_TABLESTATS; optionally mysql.innodb_table_stats / innodb_index_stats with administrative access |
| Column histogram | Distribution of values in one eligible column | INFORMATION_SCHEMA.COLUMN_STATISTICS; ANALYZE TABLE ... UPDATE/DROP HISTOGRAM |
| Exact aggregation query | The exact rows in the current snapshot | SELECT value, COUNT(*) ... GROUP BY value; accurate for that execution but too expensive to run as optimizer planning for every query |
Observe the skew before “fixing” anything
The lab’s channel column is intentionally skewed: most rows are portal-originated, while monitor-originated work is rare. Start by removing any histogram created during experimentation, then compare estimated and actual filter rows.
USE servicehub_plan_lab;-- Verify the baseline has no channel histogram. If a row exists because of a prior-- experiment, run: ANALYZE TABLE work_orders DROP HISTOGRAM ON channel;SELECT SCHEMA_NAME,TABLE_NAME,COLUMN_NAMEFROM INFORMATION_SCHEMA.COLUMN_STATISTICSWHERE SCHEMA_NAME='servicehub_plan_lab' AND TABLE_NAME='work_orders' AND COLUMN_NAME='channel';ANALYZE TABLE work_orders;SELECT @@innodb_stats_persistent AS persistent_stats, @@innodb_stats_auto_recalc AS auto_recalc;SELECT channel,COUNT(*) AS exact_rowsFROM work_ordersGROUP BY channel ORDER BY exact_rows DESC;EXPLAIN FORMAT=TREESELECT work_order_id,customer_idFROM work_ordersWHERE channel='monitor';EXPLAIN ANALYZESELECT work_order_id,customer_idFROM work_ordersWHERE channel='monitor';Expected data state: monitor is only about two percent of the deterministic lab rows. Your initial estimated filtered rows may be much less accurate than the exact count because there is no channel index or histogram. Do not require the estimate to be wrong by one specific percentage; sampling and optimizer heuristics can change across builds.
Refresh index statistics first—but know what that can solve
ANALYZE TABLE work_orders refreshes key distribution statistics. It does not automatically turn every nonindexed column into a distribution-aware estimate. If the problem is specifically a skewed nonindexed column, repeatedly analyzing index statistics may leave the estimate gap essentially unchanged.
SHOW INDEX FROM work_orders;SELECT NAME,STATS_INITIALIZED,NUM_ROWS,CLUST_INDEX_SIZE,OTHER_INDEX_SIZEFROM INFORMATION_SCHEMA.INNODB_TABLESTATSWHERE NAME='servicehub_plan_lab/work_orders';ANALYZE TABLE work_orders;EXPLAIN FORMAT=TREESELECT work_order_id FROM work_orders WHERE channel='monitor';The tempting but ineffective tuning change here is running ANALYZE TABLE repeatedly and assuming each run must improve the query. If the access path remains a scan and the channel estimate remains poor, that evidence tells you to investigate a different statistics source—or an index if the workload truly needs one.
The persistent-statistics tables in the mysql schema can provide additional detail such as last-update times, but ordinary application accounts should not be granted broad mysql-schema privileges merely for a course exercise. Use the Information Schema views and SHOW statements for the mandatory lab; treat mysql.innodb_*_stats inspection as an administrator-only extension.
Create a histogram for the skewed column
Generate a modest histogram on channel and inspect the dictionary view. The number of buckets is a resource/precision choice, not a “higher is always better” knob.
ANALYZE TABLE work_orders UPDATE HISTOGRAM ON channel WITH 16 BUCKETS;SELECT SCHEMA_NAME,TABLE_NAME,COLUMN_NAME, JSON_EXTRACT(HISTOGRAM,'$."number-of-buckets-specified"') AS buckets_requested, JSON_EXTRACT(HISTOGRAM,'$."histogram-type"') AS histogram_type, JSON_EXTRACT(HISTOGRAM,'$."sampling-rate"') AS sampling_rateFROM INFORMATION_SCHEMA.COLUMN_STATISTICSWHERE SCHEMA_NAME='servicehub_plan_lab' AND TABLE_NAME='work_orders' AND COLUMN_NAME='channel';EXPLAIN FORMAT=TREESELECT work_order_id,customer_idFROM work_ordersWHERE channel='monitor';EXPLAIN ANALYZESELECT work_order_id,customer_idFROM work_ordersWHERE channel='monitor';Expected qualitative result: the optimizer now has distribution information that distinguishes rare monitor values from common portal values. The estimated filter rows should often move closer to actual rows. The physical access may still be a table scan because a histogram informs selectivity—it is not an index.
Does a better estimate change a join plan?
Now place the skewed predicate inside a join where selectivity can influence join-order choice. Compare plans before and after the histogram on your machine. The lesson does not promise a plan flip; a stable plan with better estimates is still useful evidence.
EXPLAIN ANALYZESELECT c.segment,COUNT(*) AS nFROM work_orders AS wJOIN customers AS c ON c.customer_id=w.customer_idWHERE w.channel='monitor' AND c.tenant_id BETWEEN 1 AND 20GROUP BY c.segment;-- Optional controlled comparison: remove the histogram, re-explain, then restore it.ANALYZE TABLE work_orders DROP HISTOGRAM ON channel;EXPLAIN FORMAT=TREESELECT c.segment,COUNT(*) AS nFROM work_orders AS wJOIN customers AS c ON c.customer_id=w.customer_idWHERE w.channel='monitor' AND c.tenant_id BETWEEN 1 AND 20GROUP BY c.segment;ANALYZE TABLE work_orders UPDATE HISTOGRAM ON channel WITH 16 BUCKETS;If join order changes, capture both plans and the row estimates that motivated the choice. If it does not, explain why: available indexes, table sizes, and join costs can make the same plan cheapest even after better selectivity information.
Statistics freshness and plan stability
Persistent InnoDB statistics are enabled by default in MySQL 8.4. Automatic recalculation can occur after substantial table changes, but it is asynchronous. When you need immediately refreshed index statistics after a large load or migration, an explicit ANALYZE TABLE gives you a controlled synchronization point. Histograms are separately managed; current MySQL also supports manual or automatic histogram update modes.
Refreshing statistics has work and locking/metadata consequences, especially on large tables. Use it after evidence of stale estimates, substantial controlled data changes, or as part of a tested maintenance process—not because “every table needs ANALYZE every hour.”
Lab cleanup and verification
SELECT SCHEMA_NAME,TABLE_NAME,COLUMN_NAMEFROM INFORMATION_SCHEMA.COLUMN_STATISTICSWHERE SCHEMA_NAME='servicehub_plan_lab';SHOW INDEX FROM work_orders;SELECT VERSION() AS server_version;-- Keep the channel histogram for later comparison; if you want a clean reset:-- ANALYZE TABLE work_orders DROP HISTOGRAM ON channel;Knowledge check
- What is the difference between an index statistic and a histogram?
- Does ANALYZE TABLE always make a query faster?
- Why can a histogram improve a plan even though it is not an index?
- Where can histogram metadata be inspected?
- Why compare estimated rows with actual rows?
Reveal answers
- Index statistics estimate properties of indexes/key distributions; a histogram describes the value distribution of an eligible table column and can improve selectivity estimates without creating an access path.
- No. It refreshes statistics. The plan may remain unchanged, and a statistics refresh cannot substitute for a missing access path or bad query design.
- Better selectivity estimates can change estimated row flow and therefore cost comparisons or join order.
- INFORMATION_SCHEMA.COLUMN_STATISTICS.
- The gap identifies where optimizer information is weak and helps distinguish an estimation problem from an execution/access-path problem.
Summary and next step
Statistics are inputs to planning, not performance magic. You now know how to detect a skew-driven estimate gap, refresh index statistics, add a histogram, and judge whether the change improved the optimizer’s model. Lesson 4 moves from estimates to strategy transformations: hash joins, semijoins/antijoins, and merging versus materialization.