Unlock MySQL’s Optimizer Trace: Reveal the ‘Why’ Behind Execution Plans

What Is Optimizer Trace?

Optimizer Trace is a MySQL feature that records the entire lifecycle of a SQL statement—parsing, optimization, and execution—into the information_schema.optimizer_trace metadata table, allowing you to investigate why the optimizer chose a particular execution plan.

How to Enable Optimizer Trace

Because the feature is resource‑intensive, it is disabled by default. Enable it by setting the following system variables:

show variables like 'optimizer_trace%';
+------------------------------+---------------------------------------------------+
| Variable_name                | Value                                             |
+------------------------------+---------------------------------------------------+
| optimizer_trace             | enabled=off,one_line=off                         |
| optimizer_trace_features    | greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=on |
| optimizer_trace_limit        | 1                                                |
| optimizer_trace_max_mem_size| 1048576                                          |
| optimizer_trace_offset      | -1                                               |
+------------------------------+---------------------------------------------------+

Key variable meanings:

optimizer_trace : enabled=on/off toggles the feature; one_line=on/off controls JSON formatting.

optimizer_trace_limit and optimizer_trace_offset : work like a LIMIT clause to restrict the number of trace records displayed.

optimizer_trace_max_mem_size : maximum memory allocated for storing trace results.

optimizer_trace_features : switches for specific trace capabilities.

end_markers_in_json : adds comment markers to improve readability of the JSON output.

Only the following statement types can be traced: SELECT, TABLE, VALUES, WITH, INSERT, REPLACE, UPDATE, DELETE, EXPLAIN, SET (excluding Optimizer Trace parameters), DO, and procedural statements such as DECLARE, CASE, IF, RETURN, and CALL.

In practice, most tracing focuses on SELECT statements.

Metadata Table Structure

desc information_schema.optimizer_trace;
+-----------------------------------+----------------+------+-----+---------+-------+
| Field                             | Type           | Null | Key | Default | Extra |
+-----------------------------------+----------------+------+-----+---------+-------+
| QUERY                             | varchar(65535) | NO   |     | NULL    |       |
| TRACE                             | varchar(65535) | NO   |     | NULL    |       |
| MISSING_BYTES_BEYOND_MAX_MEM_SIZE | int            | NO   |     | NULL    |       |
| INSUFFICIENT_PRIVILEGES           | tinyint(1)     | NO   |     | NULL    |       |
+-----------------------------------+----------------+------+-----+---------+-------+

QUERY : the original SQL statement.

TRACE : JSON‑encoded trace result (controlled by end_markers_in_json).

MISSING_BYTES_BEYOND_MAX_MEM_SIZE : number of bytes omitted when the trace exceeds optimizer_trace_max_mem_size.

INSUFFICIENT_PRIVILEGES : indicates whether the executing user lacks privileges for stored routines; 0 means sufficient, 1 means insufficient, and the TRACE field will be empty.

Enabling Optimizer Trace – Step by Step

set optimizer_trace='enabled=on';
set optimizer_trace_limit=10;
set optimizer_trace_offset=-10;
set end_markers_in_json=on;

Note: Changing any Optimizer Trace variable clears the metadata table.

select count(*) from information_schema.optimizer_trace;
+----------+
| count(*) |
+----------+
|       10 |
+----------+

After resetting optimizer_trace_offset=-2, the table becomes empty because the previous records are discarded.

select count(*) from information_schema.optimizer_trace;
+----------+
| count(*) |
+----------+
|        0 |
+----------+

Examining Optimizer Trace Output

Using a simple statement such as DO 1+1 demonstrates the trace structure:

select query,trace from information_schema.optimizer_trace\G
*************************** 1. row ***************************
query: do 1+1
trace: {
  "steps": [
    {"join_preparation": {"select#": 1, "steps": [{"expanded_query": "/* select#1 */ select (1 + 1) AS `1+1`"}] }},
    {"join_optimization": {"select#": 1, "steps": []}},
    {"join_execution": {"select#": 1, "steps": []}}
  ]
}

The JSON contains a top‑level steps array with three keys:

join_preparation : SQL rewriting and keyword identification; the expanded_query field shows the internally rewritten SQL.

join_optimization : Logical and physical optimizations based on table statistics.

join_execution : The final execution plan chosen by the optimizer.

This is the first part of a multi‑article series on Optimizer Trace.