In this lesson, you will learn how to use the Optimizer Trace to peek inside the "black box" of the MySQL query optimizer and understand exactly why it chose a specific execution plan.
Moving Beyond EXPLAIN
While EXPLAIN shows you the final result of the optimizer's choices—such as which index was picked or how rows are joined—it doesn't explain the reasoning behind those decisions. The Optimizer Trace allows you to view the internal cost-based calculations, pruning logic, and alternative plans the engine considered before settling on the final execution path.
Enabling and Using the Trace
The trace is a JSON document generated during query execution. To capture it, you must toggle the session flag, run your query, and then query the information_schema.OPTIMIZER_TRACE table.
-- 1. Enable tracing for the current session
SET optimizer_trace="enabled=on";
-- 2. Execute the query you want to analyze
SELECT * FROM orders WHERE customer_id = 500 AND status = 'pending';
-- 3. Retrieve the trace result
SELECT trace FROM information_schema.OPTIMIZER_TRACE;
-- 4. Disable tracing to save resources
SET optimizer_trace="enabled=off";
Analyzing the Decision Process
When you inspect the JSON output, look for sections like join_optimization and considered_access_paths. You will see blocks labeled rows_estimation and cost_calculation.
For example, if the optimizer chooses a full table scan instead of an index you think is superior, the trace will explicitly state why: it might have calculated that the index scan would involve too many "lookups" or that the selectivity of the index is too low (based on the cardinality stats you learned about in previous lessons). You might see a pruning entry explaining that the optimizer discarded a certain index because it estimated the cost of reading the data pages would be higher than a sequential scan.
This level of detail is vital when your EXPLAIN output suggests a plan that feels inefficient, as it reveals the specific assumptions the optimizer made about your data distribution.
Key Takeaway: The Optimizer Trace is your diagnostic tool for deconstructing the cost-based logic behind execution plans, allowing you to identify exactly why the database engine favored one access path over another.