DEV Community

Cover image for What is MySQL's Optimizer Trace? Why is it Essential for SQL Optimization?
Rebooter.S
Rebooter.S

Posted on

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

#sql #mysql

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:

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)
Enter fullscreen mode Exit fullscreen mode

The key data displayed by EXPLAIN includes:

  1. Table join order
  2. Indexes considered (and potentially filtered out) by the optimizer
  3. Index actually used
  4. Estimated number of rows to be scanned for each table, based on statistics
  5. Extra data hints
  6. 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 .

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)
Enter fullscreen mode Exit fullscreen mode

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 

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)
Enter fullscreen mode Exit fullscreen mode
  • 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 

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)
Enter fullscreen mode Exit fullscreen mode

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

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)
Enter fullscreen mode Exit fullscreen mode

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.

mysql:ytt>do 1+1;
Query OK, 0 rows affected (0.00 sec)
Enter fullscreen mode Exit fullscreen mode

The corresponding Optimizer Trace result is as follows :

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)
Enter fullscreen mode Exit fullscreen mode

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.

Top comments (0)