Optimizer trace guide

You are viewing an old version of this article. View the current version here.

Optimizer trace uses the JSON format. It is basically a structured log file showing what actions were taken by the query optimizer.

A Basic example

Let's take a simple query:

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

The full trace is here. If we look at the components

mysql> select * from information_schema.optimizer_trace limit 1\G
*************************** 1. row ***************************
                            QUERY: select * from t1 where a<10
                            TRACE: 
{
  "steps": [
    {
      "join_preparation": { ... }
      }
    },
    {
      "join_optimization": {
        "select_id": 1,
        "steps": [
          { "condition_processing": { ..  } },
          { "table_dependencies": [ ... ] },
          { "ref_optimizer_key_uses": [ ... ] },
          { "rows_estimation": [ ...  ] },
          { "considered_execution_plans": [ ...  ] },
          { "attaching_conditions_to_tables": { ... } }
        ]
      }
    },
    {
      "join_execution": { ... }
    }
  ]
}

Trace structure

For each SELECT, there are two "Steps":

  • join_preparation
  • join_optimization

Join preparation shows early query rewrites. join_optmization is where most of query optimizations happen.

  • condition_processing - basic rewrites in WHERE/ON conditions.
  • ref_optimizer_key_uses - Construction of possible ways to do ref and eq_ref accesses.
  • rows_estimation - Consideration of range and index_merge accesses.
  • considered_execution_plans - Join optimization itself, that is, choice of the join order.
  • attaching_conditions_to_tables - Once the join order is fixed, parts of the WHERE clause are "attached" to tables to filter out rows as early as possible.

The above steps are for just one SELECT. If the query has subqueries, each SELECT will have these steps, and there will be extra steps/rewrites to handle the subquery construct itself.

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.