Understanding MySQL Optimizer Trace: Viewing the Full Query Optimization Process

For MySQL 5.6 and later, the optimizer_trace variable provides a way to see the complete reasoning of the query optimizer, which goes beyond the opaque output of EXPLAIN . The feature is disabled by default ( enabled=off , one_line=off ).

To turn it on, set the system variable:

SET optimizer_trace="enabled=on";

After executing the target query, the full trace can be retrieved from the information_schema.OPTIMIZER_TRACE table, which contains four columns: QUERY , TRACE (JSON text), MISSING_BYTES_BEYOND_MAX_MEM_SIZE , and INSUFFICIENT_PRIVILEGES .

The trace is divided into three stages:

prepare : initial parsing and condition processing.

optimize : cost estimation for each possible access path (range scans, index merges, etc.).

execute : the actual execution plan chosen.

During the optimize stage, the optimizer evaluates rows_estimation for each candidate index and calculates a cost value. The index with the lowest cost is selected, which is reflected in the final EXPLAIN output.

Example workflow:

Enable the trace: SET optimizer_trace="enabled=on";

Run the query you want to analyze.

Query the trace table: SELECT * FROM information_schema.OPTIMIZER_TRACE\G;

Interpret the JSON in the TRACE column to see which indexes were considered, their estimated rows, costs, and why a particular index (e.g., idx_key1 ) was finally chosen.

When finished, disable the trace: SET optimizer_trace="enabled=off";

A sample query with multiple conditions demonstrates how the optimizer evaluates three possible indexes ( idx_key1 , idx_key2 , idx_key3 ) and ultimately picks idx_key1 because its estimated cost (0.61) is far lower than the alternatives.

The detailed JSON trace also shows condition transformations (equality propagation, constant propagation, trivial condition removal), table dependencies, and the final chosen access path, helping developers understand and tune query performance.