The most instructive slow query I ever debugged was slow for a reason nobody had touched. Same query, same schema, same indexes, freshly migrated data — and a plan that chose a full scan where the old server used an index range. Nothing was misconfigured. The optimizer simply did not know what the data looked like: it could not tell a status column where 98 percent of rows say "archived" from one that is evenly distributed, because the statistics it was using described indexes, not column contents. The fix was not a hint or a rewrite. It was teaching MariaDB the shape of the data with engine-independent statistics and histograms.
This is one of the areas where MariaDB genuinely diverges from MySQL, and in my opinion for the better: MariaDB can store optimizer statistics as ordinary rows in ordinary tables — mysql.table_stats, mysql.column_stats, mysql.index_stats — readable and writable with plain SQL. Here is how the two layers of statistics work, how to turn the feature on, and the operational gotchas that decide whether it fixes your plans or fossilizes them.
Two layers of statistics, and who owns them
The layer everyone already has belongs to the engine. InnoDB maintains its own persistent statistics (innodb_stats_persistent, on by default): sampled page dives, a recalculation that triggers automatically after a meaningful fraction of the table changes, and numbers persisted in mysql.innodb_table_stats and mysql.innodb_index_stats. You can read those — even hand-edit them — but what they hold is index cardinality, not column contents, and the engine overwrites them on its own schedule. This is the same model MySQL uses, and it works fine until it does not — sampling variance on skewed data, recalcs that fire at the wrong moment, and no way to inspect what the optimizer thinks it knows.
The second layer is MariaDB's own: engine-independent statistics, stored server-side in the mysql schema. Because they are engine-independent, the same mechanism works for InnoDB, Aria, or MyRocks tables; because they are stored as data, you can SELECT them, copy them between servers, and even edit them by hand. And because they update only when you explicitly collect them, they are stable — which is the feature and the hazard in one sentence.
Turning it on and collecting
The feature is gated behind use_stat_tables. The default in current releases is PREFERABLY_FOR_QUERIES — MariaDB already prefers the stored statistics for query estimation wherever they exist — so the knob is about how far you want to push it, not about whether the feature exists. The values form an escalation: NEVER ignores the stored statistics entirely, COMPLEMENTARY uses them only where the engine cannot provide its own, PREFERABLY prefers them and falls back to the engine's, ALWAYS_FOR_QUERIES insists on them for query estimation, and the other _FOR_QUERIES variants restrict the corresponding mode to query-time estimation. PREFERABLY is the setting I run: it gives you the benefit where you have collected data without punishing tables you have not.
SET GLOBAL use_stat_tables = 'PREFERABLY';
-- Collect everything for one table (this scans the table — schedule it):
ANALYZE TABLE orders PERSISTENT FOR ALL;
-- Or scope it to the columns and indexes that matter:
ANALYZE TABLE orders PERSISTENT FOR COLUMNS (status, created_at) INDEXES (idx_status);
-- Now the part MySQL cannot show you — the statistics as data:
SELECT db_name, table_name, cardinality FROM mysql.table_stats;
SELECT column_name, min_value, max_value, nulls_ratio, avg_length, avg_frequency
FROM mysql.column_stats
WHERE db_name = 'shop' AND table_name = 'orders';
SELECT index_name, prefix_arity, avg_frequency
FROM mysql.index_stats
WHERE db_name = 'shop' AND table_name = 'orders';The PERSISTENT keyword is what makes the collection engine-independent: the numbers are written to the mysql.* statistics tables, survive restarts, and become the data use_stat_tables reads. A plain ANALYZE TABLE without it is not wasted work — it asks the storage engine to refresh its own statistics, which InnoDB persists by default — but those numbers stay in the engine's world: not histograms, not copyable between servers, not what the optimizer uses when use_stat_tables is set. Collection is a real table scan with real IO, so treat it like a backup — run it in a quiet window, and automate it, because nothing refreshes these numbers for you.
Histograms: the shape of a column
Cardinality numbers handle equality predicates — avg_frequency tells the optimizer what "status = X" is worth on average. Range predicates need the distribution, and that is what the histogram provides: MariaDB builds a height-balanced histogram by splitting the column's value range into buckets — histogram_size bytes' worth of them (254 by default, which is 127 buckets under the default double-precision format, since each bucket bound costs two bytes) — each holding roughly the same share of rows, and interpolates selectivity from the buckets a range overlaps. That is the difference between guessing that "created_at >= now - interval 1 hour" matches a twelfth of the table and knowing it matches two percent.
Three histogram types exist. SINGLE_PREC_HB is the original compact format with single-precision bucket bounds. DOUBLE_PREC_HB keeps double-precision bounds and has been the default histogram_type since MariaDB 10.4 — the precision matters on dense numeric ranges where single precision merges distinct values into the same bound. JSON_HB, added in MariaDB 10.8, stores the histogram as JSON: you can read it directly out of mysql.column_stats's histogram column and see the actual bucket boundaries, and it permits larger histograms for high-cardinality columns. Raise histogram_size when a skewed column's hot values get smeared together inside wide buckets — a status or tenant_id column with one dominant value is the classic case, and it is the classic case precisely because equality selectivity is where bad plans are born.
Why the plan differs from MySQL
If you are migrating between MySQL and MariaDB, expect plan differences and do not panic when they arrive. MySQL 8's histograms live in the data dictionary, collected with ANALYZE TABLE ... UPDATE HISTOGRAM; MariaDB's live in tables. The cost constants and the condition-filtering heuristics diverged years ago, so the same query against the same data can produce two different, individually defensible plans. MariaDB's optimizer_use_condition_selectivity variable controls how aggressively condition selectivity feeds into costing: levels 1 through 3 reproduce the legacy heuristics, level 4 — the default in current releases — lets the optimizer use the computed selectivity from your statistics and histograms, and level 5 extends that further. When a plan looks wrong, compare EXPLAIN on both sides, then run ANALYZE FORMAT=JSON on the query itself: the actual row counts next to the estimates will tell you whether the optimizer was lied to by stale statistics or made an honest mistake.
Operational gotchas
- Staleness is on you. Persistent statistics do not auto-update. A table that doubles in size or shifts its distribution keeps its old numbers, and the optimizer keeps planning for the old data. Re-collect after every bulk load and on a schedule for growing tables.
- Galera does not replicate them. The mysql.* statistics tables are written by the engine, not through replication, so each node has its own — run the collection on every node or accept that different nodes may choose different plans for the same query.
- Logical restores lose them. A dump-and-restore does not carry the statistics tables along by default, which means freshly restored servers plan with no histograms at all until you re-collect. The flip side is useful: you can copy the three tables between servers to transplant known-good statistics, which is a legitimate way to reproduce a plan from production in a staging environment.
- They are writable. You can UPDATE mysql.column_stats directly, and people do — usually to pin a plan by hand when the optimizer will not cooperate. It works, it survives restarts, and it will absolutely confuse the next person who runs ANALYZE TABLE PERSISTENT and watches their fix get overwritten. Document it if you do it.
Stale statistics are a monitoring problem
The plan that changed overnight almost never changed overnight — the data drifted for weeks while the statistics sat still, and the optimizer was the last to know. The fix is the discipline from this post: scheduled ANALYZE TABLE PERSISTENT, freshness checks on your biggest tables, histogram sizes chosen for your skew instead of the default. MonPG, the product I work on, monitors PostgreSQL today; MariaDB support is coming soon and in active development, and statistics freshness plus estimate-versus-actual drift — the ANALYZE FORMAT=JSON comparison, automated — sit high on its list of MariaDB signals worth surfacing before the postmortem. PostgreSQL fleets get that statistics-hygiene workflow today through pg_statistic_ext, default_statistics_target, and autoanalyze: see the PostgreSQL overview and the blog, or the engine comparison for the side-by-side.