Run SELECT @@optimizer_switch on a MySQL 8.x server and you get back a comma-separated wall of roughly two dozen flags. It reads like what it is: an archaeological record of every optimizer feature MySQL has shipped since 5.1, each with an on/off toggle left behind so upgrades had an escape hatch. Most teams never touch it. That is usually correct.
The problem is that optimizer_switch touches you anyway. Upgrades flip defaults. A well-meaning DBA set a flag globally during an incident in 2021 and it is still in the my.cnf. A forum answer suggests turning something off and the suggestion works, for that one query, while quietly changing plans for a thousand others. I have been bitten by all three patterns, and the flags involved are almost always the same few.
These are field notes on the flags worth understanding before they page you, accurate for MySQL 8.0 and 8.4.
How the switch actually scopes
optimizer_switch is a system variable with global and session scope, and its value behaves like a set: you assign deltas such as 'derived_merge=off' and every flag you did not mention keeps its current value. Session scope follows the usual MySQL rule, which is the first pitfall: changing the global value affects only connections opened afterward. Long-lived pool connections keep the flags they connected with, so a global flip during an incident produces a fleet where half the connections plan queries one way and half the other, which makes your incident data incoherent. If you must change a flag, prefer session scope or a per-statement hint, and treat any global change as a deploy with a rollback plan.
SELECT @@optimizer_switch;
-- Scope a change to this session only
SET SESSION optimizer_switch = 'derived_merge=off';
-- Scope a change to one statement via a hint
SELECT /*+ SET_VAR(optimizer_switch = 'block_nested_loop=off') */
o.customer_id, SUM(o.total)
FROM orders o
WHERE o.created_at >= '2026-06-01'
GROUP BY o.customer_id;derived_merge: the upgrade classic
derived_merge, on by default since 5.7, lets the optimizer merge a derived table or view reference into the outer query instead of materializing it. Merging is usually right: it exposes indexes on the base tables and avoids building a temporary result. But it changes which plans are reachable, and two failure modes recur.
The first is the upgrade regression: a query that relied on early materialization, where the derived table collapsed millions of rows to a few hundred before joining, gets merged and the collapse never happens, so the join explodes. Teams hit this moving off 5.6 and still hit it today when reviving queries that were written under materialization semantics. The second is subtler: merging moves predicates and expressions across the derived-table boundary, so a condition you thought filtered pre-aggregated rows now evaluates in a different context with different index options. When a view is suddenly slow after an upgrade or a refactor, comparing plans with derived_merge on and off for that one statement is a five-minute test that regularly ends the investigation. The durable fix is usually a rewrite or a NO_MERGE hint on the specific derived table, not a global flag change.
batched_key_access: off by default for a reason
Batched Key Access buffers join keys, sorts them, and fetches inner-table rows through a Multi-Range Read pass, turning scattered random lookups into something closer to sequential I/O. On spinning disks with large joins it was a genuine win. It ships disabled, and enabling it properly requires a small ritual: batched_key_access=on does nothing unless mrr=on and, critically, mrr_cost_based=off, because the cost model almost never chooses MRR voluntarily. That last part is the trap. mrr_cost_based=off does not mean use MRR when it helps; it means use MRR whenever the plan shape allows it, which converts a cost decision into a structural one for every query in scope.
On modern NVMe storage with buffer-pool-resident working sets, the sort-and-batch overhead frequently exceeds the sequentiality win, and BKA plans depend on join_buffer_size in ways that make them fragile across instance resizes. If you inherited a config with these three flags flipped, that is not neutral tuning history; re-test it. If you are considering enabling them, do it per statement with a BKA hint or SET_VAR and measure, never globally on faith.
block_nested_loop and the hash join sleight of hand
This flag is the best example of why you read release notes before touching optimizer_switch. In 8.0.18, MySQL added hash joins, gated behind a hash_join flag. From 8.0.20, hash join replaced block nested loop outright: BNL as an algorithm is gone, every plan that would have used it uses hash join instead, and, counterintuitively, the flag that governs this is block_nested_loop, while the old hash_join flag no longer has any effect. So on any current 8.0 or 8.4 server, block_nested_loop=off does not disable an obsolete algorithm; it disables hash joins.
The bite comes from stale configs and stale advice. A my.cnf that set block_nested_loop=off years ago to force index plans is now silently forbidding hash joins, which can turn an unindexed analytical join from seconds into hours of nested-loop grinding. The reverse surprise also happens: after an upgrade past 8.0.20, joins that used to crawl through BNL buffers now build in-memory hash tables sized by join_buffer_size, with different memory behavior per join. When a join plan looks wrong, EXPLAIN FORMAT=TREE tells you plainly which algorithm is running; tabular EXPLAIN buries it in the Extra column. The difference between what MySQL and PostgreSQL show you here is worth internalizing, and I compared them in MySQL EXPLAIN vs PostgreSQL EXPLAIN ANALYZE.
Semijoin strategies: five knobs behind one keyword
IN and EXISTS subqueries against another table are planned as semijoins, and the semijoin flag family controls the toolbox: firstmatch, loosescan, duplicateweedout, and materialization, plus the master semijoin switch itself. The optimizer picks among them by cost, which means by row estimates, which means skewed data produces bad picks. The recurring production pattern is an IN subquery whose inner side the optimizer decides to materialize when the estimate says a thousand rows and reality says fifty million, or a firstmatch plan that degenerates because the outer table grew past what the statistics remember.
-- Is semijoin planning the problem at all?
SET SESSION optimizer_switch = 'semijoin=off';
EXPLAIN FORMAT=TREE
SELECT c.id, c.email
FROM customers c
WHERE c.id IN (SELECT o.customer_id
FROM orders o
WHERE o.total > 500
AND o.created_at >= '2026-01-01');
-- Narrow to one strategy
SET SESSION optimizer_switch = 'semijoin=on,materialization=off';Disabling the master switch tells you whether semijoin planning is implicated at all; disabling one strategy at a time narrows which one misfired. Note that the materialization flag also affects non-semijoin subquery materialization, one more reason to keep experiments session-scoped. The lasting fix is almost always fresher statistics or a rewrite, because a strategy the optimizer mispriced today will be mispriced again after the next data shift.
Test one flag at a time, with receipts
The meta-pitfall is methodology. Flipping three flags at once and re-running the query tells you nothing durable; neither does testing on a cold buffer pool, or on a dev box with one percent of production data, since every one of these decisions is driven by row counts. The procedure that works:
- One flag, one statement. Use SET_VAR hints or SET SESSION so the blast radius is a single query in a single connection.
- Capture plans, not just timings. EXPLAIN FORMAT=TREE before and after, EXPLAIN ANALYZE when you can afford execution. A timing change without a plan change is noise.
- Measure against the workload, not the query. Before promoting a flag beyond a hint, compare statement digest baselines with the flag on and off across a realistic soak window; the slow log and digest tooling exists for exactly this. A flag that fixes one query and regresses ten shows up there and nowhere else.
- Prefer hints over sessions, sessions over globals. A hint documents itself in the query text. A global lives in my.cnf until someone gets paged for it in three years.
- Write down the why. Every non-default flag in production config needs a comment with a date, a ticket, and the query it was for. Undocumented optimizer flags are time bombs without a timer display.
Where MonPG fits
Flag experiments only mean something against workload history, and building that history is monitoring work. MonPG is a PostgreSQL monitoring platform today; we are building MySQL monitoring (coming soon), which will keep the digest baselines and plan evidence that make an optimizer_switch experiment provable instead of anecdotal. To be clear, MonPG does not monitor MySQL yet, and nothing in this post depends on it. If you also run PostgreSQL, where the planner has its own flag-shaped foot-guns, the platform is live and you can start there.