Table/Index Statistics, Histograms, Cardinality, and Misestimate Diagnosis

Learning outcomes

ServiceHub has an index on status, yet a query for the rare escalated value is occasionally costed like a much broader predicate. Another query filters two individually common columns whose combination is rare. These are cardinality-estimation problems. The optimizer cannot inspect every row for every candidate plan; it relies on statistical summaries. In MariaDB/InnoDB there are multiple statistical surfaces, and they have different refresh behavior and different operating costs.

1. Three statistical layers you should not conflate

USE servicehub_optimizer_lab;SELECT * FROM mysql.innodb_table_statsWHERE database_name='servicehub_optimizer_lab' AND table_name='work_orders'\GSELECT index_name,stat_name,stat_value,sample_sizeFROM mysql.innodb_index_statsWHERE database_name='servicehub_optimizer_lab' AND table_name='work_orders'ORDER BY index_name,stat_name;SELECT db_name,table_name,column_name,nulls_ratio,avg_frequency,hist_size,hist_typeFROM mysql.column_statsWHERE db_name='servicehub_optimizer_lab' AND table_name='work_orders';

An empty mysql.column_stats result is not an error. With the default use_stat_tables=preferably_for_queries, MariaDB can use engine-independent statistics when present without automatically collecting them during an ordinary ANALYZE. This is an intentional separation between “use richer statistics” and “pay to collect richer statistics.”

2. Build an estimate-versus-reality checkpoint

SELECT status,COUNT(*) AS actual_rowsFROM work_orders GROUP BY status ORDER BY actual_rows DESC;EXPLAIN FORMAT=JSONSELECT * FROM work_orders WHERE status='escalated';ANALYZE FORMAT=JSONSELECT * FROM work_orders WHERE status='escalated';EXPLAIN FORMAT=JSONSELECT * FROM work_ordersWHERE status='escalated' AND region_code='TBZ';ANALYZE FORMAT=JSONSELECT * FROM work_ordersWHERE status='escalated' AND region_code='TBZ';

The first query establishes ground truth for the teaching dataset. The EXPLAIN/ANALYZE pair then compares estimated and observed row flow. If the estimate is already close, do not force a histogram simply because the feature exists. Statistics are justified when an important predicate is misestimated enough to change plan quality.

3. Refresh InnoDB statistics first when that is the likely problem

SELECT @@innodb_stats_persistent,       @@innodb_stats_auto_recalc,       @@innodb_stats_persistent_sample_pages;ANALYZE TABLE work_orders;SELECT * FROM mysql.innodb_table_statsWHERE database_name='servicehub_optimizer_lab' AND table_name='work_orders'\G

InnoDB persistent statistics are sampled, not exact census data. The default sample-page count is a compromise between ANALYZE cost and estimate quality. Increasing sampling globally because one table is skewed can make maintenance more expensive across the server. MariaDB also supports per-table statistics settings, which are preferable when a specific table justifies a different policy. Any sample-size experiment should be staged and measured rather than copied from a tuning blog.

4. Collect targeted engine-independent histograms when needed

SELECT @@use_stat_tables,@@optimizer_use_condition_selectivity;ANALYZE TABLE work_ordersPERSISTENT FOR COLUMNS(status,region_code,priority) INDEXES(idx_status_region_sched);SELECT db_name,table_name,column_name,       min_value,max_value,nulls_ratio,avg_frequency,hist_size,hist_typeFROM mysql.column_statsWHERE db_name='servicehub_optimizer_lab' AND table_name='work_orders'ORDER BY column_name;ANALYZE FORMAT=JSONSELECT * FROM work_ordersWHERE status='escalated' AND region_code='TBZ';

MariaDB documents that engine-independent collection can perform full table/index scans and should usually be targeted rather than applied to every column and index. Histograms help with value-distribution selectivity, including non-indexed columns, but they do not magically model every cross-column dependency. If region_code and status are strongly correlated, independent summaries can still misestimate their conjunction.

5. Deliberately wrong: ANALYZE PERSISTENT FOR ALL everywhere, every hour

This converts a diagnosis tool into recurring full-scan load. It may compete with user queries for I/O and cache, increase replication activity because ANALYZE is binlogged by default, and collect expensive statistics on columns that never influence plans. It also creates false confidence: fresh histograms cannot compensate for a missing index, non-sargable predicate, unstable parameter distribution or cross-column correlation that the summary model does not capture.

-- 1. Identify an important misestimate with EXPLAIN/ANALYZE.-- 2. Collect only the relevant columns/indexes.ANALYZE LOCAL TABLE work_ordersPERSISTENT FOR COLUMNS(status,region_code) INDEXES(idx_status_region_sched);-- 3. Re-check plan/runtime evidence.ANALYZE FORMAT=JSONSELECT * FROM work_ordersWHERE status='escalated' AND region_code='TBZ';

LOCAL / NO_WRITE_TO_BINLOG prevents the ANALYZE statement from being written to the binary log when that is appropriate for your topology. Do not apply it mechanically: decide whether statistics should be collected independently on replicas/primaries and document that operational policy.

6. Statistics can be fresh and still wrong enough

Consider customer_id and region_code: in a well-designed model, customer region and work-order region may be correlated by business rules. Marginal distributions do not capture the full joint distribution. The optimizer may also face expressions, ranges, LIKE prefixes, NULL behavior, rapidly changing queues or parameter classes whose selectivity is hard to summarize. “We ran ANALYZE” is therefore not the end of diagnosis.

When estimates remain poor, verify data correctness, consider a better composite index or generated-column representation, simplify non-sargable predicates, split radically different parameter classes, or use a narrowly scoped hint only after proving the better alternative. Editing system statistics tables by hand is possible in MariaDB but belongs to controlled expert diagnostics, not routine application tuning.

7. Correlation, parameter classes, and why “fresh statistics” can still mislead

A histogram on status can describe the distribution of status values, and another on region_code can describe regions, but the optimizer does not thereby obtain a perfect joint probability model for status='escalated' AND region_code='TBZ'. Real production data often contains business correlation: one product tier appears mostly in one geography, one incident type belongs to one device family, or recent rows have very different status distributions from historical rows. Fresh single-column statistics can therefore remain insufficient.

Separate parameter classes are equally important. A prepared statement may receive a rare tenant ID in one request and a dominant tenant in another. If one plan is expected to serve both, test both classes explicitly. If the classes have radically different optimal access paths, the durable solution may be schema/query redesign, partitioning of workloads, or explicit application routing—not endlessly increasing histogram size.

The goal is not maximum statistical detail. The goal is enough accurate information for important plan decisions at acceptable maintenance cost. Collecting everything can itself become a production workload.

8. A statistics change needs rollback evidence too

Before collecting expensive engine-independent statistics on a large production table, record the current plan, query latency distribution, statistics rows, server load, and replication policy. After collection, compare the same query classes. If plans regress, you need a documented way to stop preferring those statistics or restore the prior operational state. Treat statistics as optimizer inputs with lifecycle and ownership, not invisible metadata that can never cause regressions.

9. Verification and bridge

Lesson 4 now studies how these estimates feed join algorithms, semijoin strategies, derived-table merge/materialization and subquery transformations.

Authoritative references

• MariaDB Documentation — ANALYZE TABLE
• MariaDB Documentation — Engine-Independent Table Statistics
• MariaDB Documentation — Histogram-Based Statistics
• MariaDB Documentation — InnoDB Persistent Statistics