The query that sent me down the optimizer_trace rabbit hole was a tenant-scoped order search that ran in 40 milliseconds for years and then, one Tuesday, took 9 seconds. Nothing had deployed. EXPLAIN showed it using the status index instead of the composite tenant index, and EXPLAIN could not answer the only question that mattered: why does the optimizer think this is cheaper? ANALYZE TABLE fixed it in seconds, but I wanted to see the reasoning, not just the verdict. That reasoning is exactly what optimizer_trace records.
optimizer_trace is the optimizer's own log of its decision process: which access paths it considered, what each was estimated to cost, and why the losers lost. It is verbose, JSON-formatted, mildly expensive to capture, and the only first-party tool that shows rejected plans. Here is how I capture it safely, how I read it, and when plain EXPLAIN is honestly enough.
How do you capture a trace without disturbing production?
Session by session, never globally. The trace is controlled by a small set of session variables: enable it, size the buffer, run the statement, read the result, disable it. While enabled it allocates a trace buffer for every statement the session runs and adds measurable overhead to each one, so a globally enabled trace on a busy server is a self-inflicted slowdown. The capture pattern:
SET optimizer_trace = 'enabled=on';
SET optimizer_trace_max_mem_size = 1000000;
SET optimizer_trace_limit = 1;
SELECT o.order_id, o.created_at, c.email
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.status = 'refunded'
AND o.tenant_id = 9182
ORDER BY o.created_at DESC
LIMIT 20;
SELECT TRACE
FROM information_schema.OPTIMIZER_TRACE;
SET optimizer_trace = 'enabled=off';Each traced statement appends a row to OPTIMIZER_TRACE, and optimizer_trace_limit caps how many are kept — 1 by default, with optimizer_trace_offset available when you need to page through a batch of statements. The table also reports MISSING_BYTES_BEYOND_MAX_MEM_SIZE, which is your truncation alarm, and INSUFFICIENT_PRIVILEGES for permission problems. Trace the exact statement with its real literal values; prepared-statement placeholders and different constants can produce different range estimates and therefore a different plan, which is usually the very behavior you are investigating.
How do you read considered_execution_plans?
From the inside out, following the cost. The trace JSON has three big steps: join_preparation, where the query is rewritten into internal form; join_optimization, the interesting part; and join_execution. Inside join_optimization, rows_estimation lists per-table access options with their estimated rows and cost — table scans, potential range scans per index, const-table detection. Then considered_execution_plans walks the join order greedily: plan_prefix accumulates tables one by one, and at each step you see the candidate next tables with access type, estimated rows, cumulative cost, and whether that branch survived pruning. The winner is simply the complete plan with the lowest total cost, marked with "chosen": true. The cost numbers are arbitrary units — meaningful only relative to each other within that trace on that version — so never compare a cost to milliseconds, and never compare costs across MySQL releases.
Where does rows_estimation diverge from reality?
Wherever statistics are stale, and that divergence is what a wrong-index case looks like up close. My Tuesday query traced like this: rows_estimation showed the range on the status index estimated at 1.4 million rows, because the index statistics claimed refunded rows were 40 percent of the table, while the tenant_id range on the composite index estimated 900,000 rows. The optimizer did its job correctly — given those inputs, status really did look cheaper. The inputs were fiction: refunds were 0.3 percent of the table, roughly 40,000 rows, and the statistics had last been computed right after a bulk purge doubled the refund share. ANALYZE TABLE recomputed them, the estimates flipped by two orders of magnitude, and the composite index won on the very next trace. This is the failure shape I look for first in any wrong-plan mystery, and it rhymes with the one in ORDER BY LIMIT picking the wrong index: the plan is rarely crazy, the statistics usually are. Two related dials worth knowing: eq_range_index_dive_limit, default 200 in 8.0, decides how many equality ranges trigger real index dives before the optimizer falls back to statistics — the mechanics are in index statistics and index dives — and optimizer_switch decides which strategies are even candidates, a knob with its own graveyard of mistakes in optimizer_switch pitfalls.
What do you do when the trace is truncated?
Raise optimizer_trace_max_mem_size for the session and capture again. The default buffer is 1MB in 8.0 — it was a painful 16KB in 5.7, which truncated nearly everything interesting — and a many-table join with range analysis on every table can still blow past it, at which point the trace simply stops mid-JSON and MISSING_BYTES_BEYOND_MAX_MEM_SIZE tells you how many bytes you lost. For serious spelunking I set 10 to 50MB for the session; it is per-connection memory, freed when you disable tracing, so the cost of generosity is low. If the trace is complete but overwhelming, shrink the problem instead: trace a simplified form of the query with the same join core, because the decision you care about usually survives the simplification and the JSON drops to a fraction of the size.
When is EXPLAIN FORMAT=JSON enough?
Whenever you only need the chosen plan and its estimated cost breakdown — which is most days. FORMAT=JSON shows access type, key, rows_examined_per_scan, filtered, and a cost_info block with read_cost, eval_cost, and total query_cost, all without the overhead or the megabyte of JSON. EXPLAIN ANALYZE goes further and shows actual rows and time per iterator, and it has become my first stop for any slow query; the reading workflow is in the EXPLAIN ANALYZE guide. Reach for optimizer_trace when the chosen plan is indefensible given the data — when you need the rejected plans, the "cause" fields explaining why an index was not usable, or the exact estimate that tipped the scales. It is the difference between reading the verdict and reading the trial transcript: you want the transcript only when the verdict makes no sense.
Where MonPG stands on MySQL
I build MonPG, so up front: MonPG monitors PostgreSQL today, and MySQL support is in active development, not shipped. Plan regressions like the Tuesday query are exactly what good monitoring should catch from the outside — a digest whose rows-examined jumped by three orders of magnitude after an ANALYZE TABLE is a plan flip with a timestamp — and that digest-first view of plan health is central to the MySQL work. The MySQL monitoring (coming soon) page tracks it as it lands. Until then the same evidence-first approach runs on the PostgreSQL side, and more MySQL field notes are on the blog.