What is MySQL's Optimizer Trace? Why is it Essential for SQL Optimization?

Introduction

For relational databases, the design of tables and SQL is written are particularly crucial. It wouldn’t be an exaggeration to say that they account for 90% of performance. So this time, specifically targeting these two major knowledge areas, we’ll conduct a detailed analysis for you, peeling back the layers.

This Series uses plain and understandable language and selects a large number of examples to elaborate on the subtleties for you.

🧑‍💻 Target audience:

DBA
Database developers
Students

We will use MySQL as the demonstration database.

In previous chapters, we have covered the interpretation of MySQL execution plans in great detail. Today, we will extend this topic to discuss the execution plan tracing feature, known as MySQL’s Optimizer Trace.

First, let’s recall the result of an EXPLAIN statement:

1
2
3
4
5
6
7
8
mysql:ytt>explain select * from t1 a left join y1 b on a.id = b.id where a.r1<100 order by a.r2 desc;
+----+-------------+-------+------------+--------+---------------+---------+---------+----------+--------+----------+-----------------------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref      | rows   | filtered | Extra                       |
+----+-------------+-------+------------+--------+---------------+---------+---------+----------+--------+----------+-----------------------------+
|  1 | SIMPLE      | a     | NULL       | ALL    | idx_r1        | NULL    | NULL    | NULL     | 998222 |    50.00 | Using where; Using filesort |
|  1 | SIMPLE      | b     | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | ytt.a.id |      1 |   100.00 | NULL                        |
+----+-------------+-------+------------+--------+---------------+---------+---------+----------+--------+----------+-----------------------------+
2 rows in set, 1 warning (0.00 sec)

The key data displayed by EXPLAIN includes:

Table join order
Indexes considered (and potentially filtered out) by the optimizer
Index actually used
Estimated number of rows to be scanned for each table, based on statistics
Extra data hints
Additional cost data shown by specific EXPLAIN formats (explain format=tree / explain format=json)

While the results from EXPLAIN are sufficient for quick SQL tuning, they do not explain why the MySQL optimizer chose a particular execution plan. To gain a deeper understanding, Optimizer Trace is necessary .

For instance:

Why did the optimizer choose a full table scan for table a even though index idx_r1 exists?
For the join between the two tables, why was the sequence table a driving table b chosen, and not the other way around?
Why did the query require a filesort even though field r2 has an index?

To answer these “why” questions, we introduce MySQL’s Optimizer Trace feature.

1. What is Optimizer Trace?

In simple terms, Optimizer Trace is a detailed tracker for the SQL execution plan. It records the query’s parsing, optimization, and execution process into a MySQL metadata table (information_schema.optimizer_trace). Analyzing this trace reveals the reasons behind the optimizer’s decisions .

2. How to Use Optimizer Trace?

To use the Optimizer Trace feature, you must first enable it. Important: This feature can be resource-intensive and is disabled by default. It can be enabled by adjusting session variables .

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
mysql:ytt>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                                                                         |
+------------------------------+----------------------------------------------------------------------------+
5 rows in set (0.00 sec)

Here is an explanation of the key parameters :

optimizer_trace: enabled=on/off enables/disables the Optimizer Trace feature; one_line=on/off controls JSON formatting for storage; typically left as off for readability.
optimizer_trace_limit / optimizer_trace_offset: These parameters work like the LIMIT clause, controlling the number of trace records displayed. Showing more traces consumes more memory. The default is to show the most recent trace. For example, setting optimizer_trace_limit to 10 and optimizer_trace_offset to -10 displays up to 10 trace records.
optimizer_trace_max_mem_size: The maximum memory allocated for storing trace results.
optimizer_trace_features: Controls which specific trace features are enabled or disabled.
end_markers_in_json: Enables/disables adding comments within the JSON trace for better readability.
Optimizer Trace can track various statements, including :
- SELECT, TABLE, VALUES, WITH, INSERT, REPLACE, UPDATE, DELETE
- EXPLAIN
- SET (excluding settings related to Optimizer Trace variables)
- DO
- DECLARE, CASE, IF, RETURN statements within stored functions/triggers
- CALL

In database tuning, the focus is often on SELECT statements, so tracing typically applies to them.

Metadata Table Structure

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
mysql:ytt>desc information_schema.optimizer_trace;
+-----------------------------------+----------------+------+-----+---------+-------+
| Field                             | Type           | Null | Key | Default | Extra |
+-----------------------------------+----------------+------+-----+---------+-------+
| QUERY                             | varchar(65535) | NO   |     |         |       |
| TRACE                             | varchar(65535) | NO   |     |         |       |
| MISSING_BYTES_BEYOND_MAX_MEM_SIZE | int            | NO   |     |         |       |
| INSUFFICIENT_PRIVILEGES           | tinyint(1)     | NO   |     |         |       |
+-----------------------------------+----------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

QUERY: The text of the traced SQL statement.
TRACE: The trace result for the SQL statement, stored in JSON format (influenced by the end_markers_in_json variable).
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: Indicates the number of bytes truncated if the trace result exceeds the optimizer_trace_max_mem_size limit.
INSUFFICIENT_PRIVILEGES: Indicates if the user lacks privileges (e.g., for stored procedures/functions with SQL SECURITY DEFINER). 0 means privileges are sufficient, 1 means they are not, and the TRACE field will be empty .

Steps to Enable Optimizer Trace

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
mysql:ytt>set optimizer_trace='enabled=on';
Query OK, 0 rows affected (0.00 sec)

mysql:ytt>set optimizer_trace_limit=10;
Query OK, 0 rows affected (0.00 sec)

mysql:ytt>set optimizer_trace_offset=-10;
Query OK, 0 rows affected (0.00 sec)

mysql:ytt>set end_markers_in_json=on;
Query OK, 0 rows affected (0.00 sec)

Important: Modifying any Optimizer Trace-related parameter clears the information_schema.optimizer_trace table .

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
mysql:ytt>select count(*) from information_schema.optimizer_trace;
+----------+
| count(*) |
+----------+
|       10 |
+----------+
1 row in set (0.00 sec)

mysql:ytt>set optimizer_trace_offset=-2;
Query OK, 0 rows affected (0.00 sec)

mysql:ytt>select count(*) from information_schema.optimizer_trace;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

3. Understanding Optimizer Trace Results

Let’s examine the basic structure of an Optimizer Trace result using a simple DO statement, which is very simple and used only to verify syntax correctness without returning a result set.

1
2
mysql:ytt>do 1+1;
Query OK, 0 rows affected (0.00 sec)

The corresponding Optimizer Trace result is as follows :

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
mysql:ytt>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": [
        ]
      }
    }
  ]
}
1 row in set (0.00 sec)

The Optimizer Trace result is a JSON object. The main key is "steps", whose value is an array. This array typically contains three main stages, each representing a phase of query processing :

join_preparation (Preparation Stage): This phase involves SQL query rewriting, keyword recognition, etc. The expanded_query value shows the internal SQL after rewriting.
join_optimization (Optimization Stage): This is the core phase for SQL optimization, including logical optimizations and physical optimizations based on table statistics and cost estimates. It details the evaluation of different access paths, join methods, and costs .
join_execution (Execution Stage): This phase shows the final execution plan being carried out.

This article serves as an introduction to Optimizer Trace. Due to the depth of content, I have split it into several parts. Please stay tuned for subsequent articles.

👋 See you in the next lesson.

What is SQLFlash?

SQLFlash is your AI-powered SQL Optimization Partner.

Based on AI models, we accurately identify SQL performance bottlenecks and optimize query performance, freeing you from the cumbersome SQL tuning process so you can fully focus on developing and implementing business logic.