The most disorienting bug report I ever got after a MySQL-to-MariaDB migration was not a wrong result or a replication break. It was a query — unchanged, against migrated data with identical indexes — that ran in 40 milliseconds on the old MySQL 5.7 box and 11 seconds on the new MariaDB 10.6 box. The plan was different. Not worse in some subtle cost-estimate way; a different join order, a different access method, a full scan where there used to be an index range. Nothing was broken. The optimizer was simply a different piece of software than the one that wrote the old plan, and it had made a different defensible choice that happened to be catastrophically wrong for this data distribution.
This is the thing people underestimate about the MariaDB/MySQL fork: it is not just a governance difference and a logo. The query optimizers diverged more than a decade ago and have been developed independently ever since. If you run MariaDB, or you are moving between the two, you need to know where they genuinely differ. This post covers the three areas that matter most in practice: optimizer_switch, statistics collection, and the subquery and derived-table machinery.
optimizer_switch: same name, different defaults, different switches
Both databases expose optimizer behavior through the optimizer_switch system variable, and the superficial similarity is a trap. MariaDB's list of switches is longer, the defaults differ, and several switches exist on only one side. The differences that have bitten me in production:
- MRR is off by default in MariaDB. MariaDB's optimizer_switch defaults to mrr=off (with mrr_cost_based=off as well), meaning multi-range read is simply disabled until you enable it. MySQL defaults mrr=on, gated behind its own cost check. Range-heavy queries on secondary indexes can behave differently purely because of this.
- subquery_cache is MariaDB-only. MariaDB can cache the results of a correlated subquery for the duration of a statement, keyed by the correlation values, so the subquery executes once per distinct outer value instead of once per outer row. It defaults on and is usually invisible — until you notice a subquery re-executed per row on one engine and cached on the other.
- Derived table handling diverged early. Both servers try to merge a derived table into the outer query first (derived_merge defaults on in each). The difference is what happens when a derived table cannot be merged — grouping, unions, and the like: MariaDB materializes it and can add automatic keys (derived_with_keys=on by default), so joins against the materialized subquery or view can use real indexed lookups. MySQL 8.0 also adds automatic keys to materialized derived tables, so the presence of the feature is no longer the divergence; what still differs is the surrounding cost model that decides when to merge versus materialize in the first place. A view that is fast on MariaDB because it got a good auto-key can be slow on MySQL, and the reverse happens when MySQL's merge produces the better plan.
- Condition pushdown switches differ. MariaDB added condition_pushdown_for_derived and condition_pushdown_from_having years ago; the names and defaults simply do not line up across the two servers.
The operational move is boring and essential: before migrating, capture SELECT @@optimizer_switch from the old server, and after migrating, diff it against the new one. When a plan regresses, your first question should be "which switch is different" before it is "is the optimizer broken."
SELECT @@optimizer_switch;
-- Session-scoped experiment while diagnosing a plan regression:
SET SESSION optimizer_switch = 'mrr=on,mrr_cost_based=off';
EXPLAIN
SELECT o.order_id, o.total
FROM orders o
WHERE o.customer_id = 4812
AND o.created_at >= '2025-01-01'
ORDER BY o.created_at DESC
LIMIT 50;Statistics: histograms you own vs histograms the dictionary owns
Both engines now have histograms, but they got there by different roads and the operational ergonomics are completely different. MySQL 8.0 stores histograms in the data dictionary via ANALYZE TABLE ... UPDATE HISTOGRAM, tied to its own cost model and its own persistence layer. MariaDB built engine-independent statistics — EITS — much earlier, and it shows in the design: the statistics live in ordinary tables in the mysql database (table_stats, column_stats, index_stats), they are engine-agnostic by construction, and you collect them with a deliberately explicit statement.
-- Collect persistent statistics, including histograms, for a table:
ANALYZE TABLE orders PERSISTENT FOR ALL;
-- Histograms for a specific skewed column, sized deliberately:
SET SESSION histogram_size = 200;
SET SESSION histogram_type = 'DOUBLE_PREC_HB';
ANALYZE TABLE order_events PERSISTENT FOR COLUMNS (status, region) INDEXES (idx_status);
-- See what was collected:
SELECT * FROM mysql.column_stats
WHERE table_name = 'order_events';
SELECT * FROM mysql.table_stats
WHERE table_name = 'order_events';Three details matter here. First, use_stat_tables controls how far the optimizer actually consults EITS. Since MariaDB 10.4 it defaults to 'preferably_for_queries', so freshly collected statistics are used for query planning out of the box — but the knob still bites: servers upgraded through the years sometimes carry use_stat_tables='never' pinned in my.cnf, and the alternatives ('complementarily' to augment engine statistics, 'preferably' to prefer EITS) change how much weight your histograms get. I have watched teams collect perfect histograms and then wonder why nothing changed; the variable, inherited from an old config, was the answer. Second, because the stats live in ordinary tables, you can dump them with mariadb-dump, copy them between servers, edit them by hand to test a hypothesis, and restore a known-good set after a bad ANALYZE. That is a genuinely useful property for testing plan behavior. Third, persistent statistics do not refresh themselves: after bulk loads or big data distribution shifts, re-run the ANALYZE or your histograms describe a table that no longer exists.
Why the plans actually diverge
Even with identical data and indexes, the two optimizers are working from different cost constants, different default statistics, and different transformation rules, so "different plan" is the expected outcome, not an anomaly. MariaDB's cost model historically baked its constants into the code — unlike MySQL 8.0, which exposes them in the server_cost and engine_cost tables in the mysql database. MariaDB 11.0 rewrote that model and exposes its own tunables as a family of optimizer_*_cost system variables, so on 11.x you can inspect and tune the cost weights directly; on 10.6 they are fixed. Its subquery materialization strategy for IN subqueries — materialize the subquery once into an indexed temporary table (a HEAP hash index for small results, an Aria or MyISAM B-tree for larger ones) and probe it by key — was pioneering when it landed and is simply different machinery from what MySQL does now. MariaDB also evaluates EXISTS-to-IN and IN-to-EXISTS transformations under its own rules, which is why EXISTS rewrites that were folklore on MySQL sometimes change nothing on MariaDB: the optimizer already did the rewrite.
The diagnostic tooling diverged too. MariaDB has had the ANALYZE statement — an executed-plan report with actual row counts (r_rows) and filtered percentages per operation, with per-operation timing available when you run it as ANALYZE FORMAT=JSON — since 10.1, years before MySQL 8.0.18 added EXPLAIN ANALYZE. If you are comparing a plan across engines, run ANALYZE on the MariaDB side rather than arguing from EXPLAIN estimates; the r_rows column tells you exactly where the optimizer's row estimate divorced from reality.
ANALYZE
SELECT c.region, SUM(o.total) AS revenue
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
WHERE o.created_at >= '2025-01-01'
GROUP BY c.region
ORDER BY revenue DESC;When the estimate and r_rows disagree by orders of magnitude at a particular operation, that is your skewed column, and that is the column that wants a histogram.
A migration playbook that actually works
If you are moving workloads between MySQL and MariaDB in either direction, the procedure that has saved me repeatedly looks like this. Before the move: enable the slow query log with a low long_query_time for a representative week and extract the top query families by total time; capture @@optimizer_switch, and collect fresh statistics on the source. On the new server, immediately after loading data: run ANALYZE TABLE ... PERSISTENT FOR ALL on the hot tables, confirm use_stat_tables is not pinned to 'never' by an inherited my.cnf (the modern default, preferably_for_queries, already uses your EITS for planning), diff optimizer_switch against the source and reconcile every difference deliberately, then replay the top query families and compare plans and timings. Do not wait for production traffic to discover the regression — the top twenty query families by total time account for almost every plan-regression incident I have ever investigated, and they fit in an afternoon of rehearsal.
If a regression appears anyway, the ladder of escalation is: check the switch diff, refresh statistics on the involved tables, add a histogram on the skewed column, and only then reach for index changes or rewrites. In my experience you rarely reach the bottom rung.
Watching the plans after the move
Plan regressions are a monitoring problem as much as an optimizer problem: the query that got 200x slower looks identical in every application log, and only response-time history per query family shows the cliff. That is the layer worth having in place before a migration, not after.
Rehearse the migration before you run it, and keep the slow logs from both sides for a month after — a plan regression shows up in response-time history long before anyone opens an EXPLAIN. That monitoring layer is where MonPG comes in for me: it monitors PostgreSQL today, not MariaDB, and MariaDB monitoring is in active development and on the way. Query-family latency history and plan-change detection after statistics refreshes are on its design list precisely because of incidents like the one that opens this post. Until it ships, the slow log plus ANALYZE is your safety net. And if your fleet also runs PostgreSQL, where pg_stat_statements gives you this visibility natively, that side of the workflow is live today — start with the PostgreSQL overview, see how the engines compare, or read more on the blog.