MySQL Optimizer Trace comes in handy in analyzing the optimizer’s actions and decisions. Want to learn more? Keep reading. 

At Bobcares, we offer solutions for every query, big and small, as a part of our MySQL Support Service.

Let’s take a look at how our Support Team is ready to help customers with MySQL Optimizer Trace.

All About MySQL Optimizer Trace

MySQL comes with a new tracing feature in the MySQL optimizer. The interface has a set of optimizer_trace_xxx system variables as well as INFORMATION_SCHEMA.OPTIMIZER_TRACE table in order to produce output understood by users as well as programs. Furthermore, it demystifies the MySQL Optimizer’s actions and decisions.

The output format is JSON and is made up of:

For instance, let’s take a look at the following query

MariaDB> explain select * from table1 where a<100;
+------+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
| id   | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                 |
+------+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
|    1 | SIMPLE      | table1    | range | a             | a    | 5       | NULL | 10   | Using index condition |
+------+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+

However, if we consider only the component names, we wind up with:

MariaDB> select * from information_schema.optimizer_trace limit 1\G
*************************** 1. row ***************************
                            QUERY: select * from table1 where a<100
                            TRACE: 
{
  "steps": [
    {
      "join_preparation": { ... }
    },
    {
      "join_optimization": {
        "select_id": 1,
        "steps": [
          { "condition_processing": { ... } },
          { "table_dependencies": [ ... ] },
          { "ref_optimizer_key_uses": [ ... ] },
          { "rows_estimation": [
              {
                "range_analysis": {
                   "analyzing_range_alternatives" : { ... },
                  "chosen_range_access_summary": { ... },
                },
                "selectivity_for_indexes" : { ... },
                "selectivity_for_columns" : { ... }
              }
            ]
          },
          { "considered_execution_plans": [ ... ] },
          { "attaching_conditions_to_tables": { ... } }
         ]
      }
    },
    {
      "join_execution": { ... }
    }
  ]
}

Each SELECT command in the Trace structure comes with 2 steps, namely:

1. 1. • join_preparation
      • join_optimization

While the former displays early query rewrites, the latter is where the query optimizations take place as seen below:

1. 1. • condition_processing
      • ref_optimizer_key_uses
      • rows_estimation
      • considered_execution_plans
      • attaching_conditions_to_tables

According to our Support Techs, the above steps are for a single SELECT query only. If the SELECT query has subqueries, each of the subqueries will have these steps as well as extra steps or rewrites in order to handle the subquery construct.

We can also extract certain parts of the trace with the following functions:

1. 1. • JSON_EXTRACT: This function extracts a part of JSON document.
      • JSON_DETAILED: This function presents the trace in a user-readable way

How to enable or view the trace as a user

We can enable or view the trace as seen below:

SET SESSION OPTIMIZER_TRACE="enabled=on"; # enable tracing
  ; # like SELECT, EXPLAIN SELECT, UPDATE, DELETE...
  SELECT * FROM information_schema.OPTIMIZER_TRACE;
  [ repeat last two steps at will ]
  SET SESSION OPTIMIZER_TRACE="enabled=off"; # disable tracing

Guidelines to add trace

Here are a few guidelines to add trace:

1. 1. • Try to keep a limit on the number of distinct words like row, tmp, and so on.
      • Use simple characters like a-z,A-Z,_,#, and so on for key names.
      • Use an array when order is essential.

With this handy guide, you will be able to use Optimizer Trace to understand the optimizer’s actions and decisions better.

[Looking for a solution to another query? We are just a click away.]

Conclusion

To sum up, our skilled Support Engineers at Bobcares demonstrated how to enable and view MySQL Optimizer Trace.