Have you ever wondered how complex queries are executed in MySQL?

With the Explain plan, we gain valuable insights into the optimizer's decision-making process, allowing us to understand how it determines the most efficient path to retrieve data. Through this powerful feature, we can fine-tune queries, optimize performance, and reduce unnecessary full-table scans, all while gaining a deeper understanding of the inner workings of MySQL's query execution. Let's explore the Explain format=tree in MySQL and uncover the secrets of query optimization.

Explain Plan Insights

The Explain plan serves as a window into the mind of MySQL's query optimizer. Like a skilled conductor, the optimizer meticulously evaluates various factors to determine the most efficient query plan. It considers the query cost, which involves a comprehensive analysis of IO, CPU, and network resources that will be utilized by the query.

Let’s figure out how the optimizer works under the hood.

Optimizer's Decision-Making Process:

The Explain plan gives an approximation of how the optimizer will execute the query. The optimizer’s cost model accounts for the IO, CPU, and network resources that will be used by the query.

This feature is beneficial in query tuning, for example, figuring out whether the query uses an index or whether indexes will be of any use. With this, we can reduce unnecessary full-table scans, minimising disk IO and execution time. To use this, the Explain keyword is added before queries.

For example,

 
EXPLAIN SELECT node_id,timestamp FROM osm.nodes;	
	

The Three Formats of Explain Plan

The Explain plan comes in three formats, each of which differs and provides an in-depth view of the optimizer's suggested plan. It's important to note that the Explain plan doesn't provide the actual query result; rather, it offers an estimate. The three formats are as follows:

• Traditional
• JSON
• Tree

To modify the format, you can use the following syntax:

 
EXPLAIN FORMAT=TREE SELECT node_id,latitude FROM nodes WHERE changeset_id > 1000000;
	

Traditional format: default view in MySQL

It doesn’t show the cost, as some other formats do, but rather it provides valuable insights by displaying the table, index, and type of scan information in a precise and easily understandable format. This detailed information helps users to better comprehend how the database optimizer plans to execute the query, allowing them to make informed decisions in query tuning and performance optimization.

Example:

 
EXPLAIN  SELECT node_id,latitude FROM nodes WHERE changeset_id > 1000000; 
	

Output:

 
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | nodes | NULL       | ALL  | changeset_id  | NULL | NULL    | NULL | 2853846 |    50.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
	

JSON format: providing detailed information

On the other hand, the JSON format presents the Explain plan in a more elaborate manner, providing a comprehensive set of details. Along with the total cost, it includes a wealth of additional information such as table-related details, possible indexes, the type of join used, filtering statistics, and more.

Example:

 
EXPLAIN  FORMAT=JSON SELECT node_id,latitude FROM nodes WHERE changeset_id > 1000000;
	

Output:

 
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "298686.60"
    },
    "table": {
      "table_name": "nodes",
      "access_type": "ALL",
      "possible_keys": [
        "changeset_id"
      ],
      "rows_examined_per_scan": 2853846,
      "rows_produced_per_join": 1426923,
      "filtered": "50.00",
      "cost_info": {
        "read_cost": "155994.30",
        "eval_cost": "142692.30",
        "prefix_cost": "298686.60",
        "data_read_per_join": "65M"
      },
      "used_columns": [
        "node_id",
        "latitude",
        "changeset_id"
      ],
      "attached_condition": "(`osm`.`nodes`.`changeset_id` > 1000000)"
    }
  }
} |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
	

Tree format: hierarchical and easy-to-read (introduced in 8.0.16)

The last format, known as the tree format, shares similarities with JSON but provides a more concise representation of the Explain plan. Although it contains limited data compared to the JSON format, it still offers essential insights into the query execution process. Let's delve deeper into the tree format and explore its specific characteristics and benefits.

Unveiling the Tree Format

• The tree format was introduced in MySQL version 8.0.16.
• It offers a clear and hierarchical view of the query execution steps taken by the database engine.
• This format makes it easier to identify potential performance issues or bottlenecks in the query.
• With the tree format, you can quickly grasp the order in which operations are executed.
• It provides a user-friendly visualization of the complex query execution flow.

Another concept similar to Explain is Explain analyze, but with a distinct feature. While Explain provides insights into the query execution plan without actually executing the query, Explain analyze goes a step further. It not only reveals the execution plan but also executes the query to obtain actual results. Moreover, Explain analyze utilizes the tree format by default, presenting a clear and detailed view of the query's execution steps and performance metrics.

Let's take a look at an example of how the tree format appears in action.

Database used: OSM

Example:

 
SELECT way_id, changeset_id, timestamp, version FROM ways where timestamp between '2011-06-14' and '2014-06-14' order by timestamp;
	

Output:

 
+------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                        |
+------------------------------------------------------------------------------------------------------------------------------------------+
| -> Sort: ways.`timestamp`  (cost=50125 rows=482976)
    -> Filter: (ways.`timestamp` between '2011-06-14' and '2014-06-14')  (cost=50125 rows=482976)
        -> Table scan on ways  (cost=50125 rows=482976)
 |
+------------------------------------------------------------------------------------------------------------------------------------------+
	

In the tree format, the root node displays the total cost of the query, and the execution direction goes from inside to outside.

Regarding supported queries, the Explain plan varies for each format and supports different types of queries:

• Traditional and JSON formats support SELECT, UPDATE, and DELETE queries.
• The Tree format specifically supports SELECT queries.

As a result, the executor will not execute UPDATE and DELETE queries using the Tree format, as illustrated in the following example:

 
EXPLAIN format=tree UPDATE tab1 SET c1 = '17' WHERE c2 = 'hello';
+----------------------------------------+
| EXPLAIN                                |
+----------------------------------------+
|   |
+----------------------------------------+


mysql> EXPLAIN format=tree DELETE FROM tab WHERE c1 = '3';
+----------------------------------------+
| EXPLAIN                                |
+----------------------------------------+
| . |
+----------------------------------------+
	

Reading the Tree Format

Understanding the hierarchical structure of the tree

In the Explain tree format, the structure resembles a typical tree with three types of nodes: the Root Node, Child Nodes, and Leaf Nodes. The query execution order follows a sequence from the Leaf Node → Child Node → Root Node, as illustrated in the diagram below.

Root Node:

The Root Node serves as the top-level node of the tree and encompasses the cost of the entire query being executed. However, not all queries display the cost at the top; it depends on the query execution order. For instance, if a query includes a limit clause, it will typically appear at the top as it is executed last in the query.

Child Node:

Directly below the Root Node, we find the Child Nodes. These nodes represent the various steps that the database engine will undertake to execute the query. Each Child Node corresponds to a specific operation performed during query execution.

Leaf Node:

At the bottom of the tree, we encounter the Leaf Nodes, which represent individual operations carried out by the database engine to execute the query. These operations can involve table scans, index lookups, or other data retrieval tasks.

Let's check it out with the example below.

 
EXPLAIN format=tree SELECT node_id, latitude,  changeset_id, timestamp FROM nodes WHERE version=5 ORDER BY timestamp desc;
	

Output:

 
+---------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                             |
+---------------------------------------------------------------------------------------------------------------+   Total cost
| -> Sort: nodes.`timestamp` DESC  (cost=2111.60 rows=20846)  Root node
    -> Filter: (nodes.version = 5)  (cost=2111.60 rows=20846) Child node
        -> Table scan on nodes  (cost=2111.60 rows=20846)     leaf node 
 |
+---------------------------------------------------------------------------------------------------------------+
	

The execution order displayed will be read from the leaf node. So a point is made clear, i.e., the cost calculated in the leaf node will be added to the child node, and the total cost will be the sum of the individual costs. To elucidate, the data from the leaf node moves to the child node and eventually reaches the root node, while each level exhibits various steps. Each step will have an individual cost. The total cost estimated will reach the root node or may remain as a total in a parent node.

Reading tree format

Now that we have covered the structure and the view of the Explain Tree format, the next step is to learn how to read it. At first glance, the arrows and nodes might seem confusing, but with a proper understanding, it becomes much easier. Let's explore how to read the Explain Tree format step by step.

• Start with the Leaf Node: To comprehend the execution of the query, begin by reading from the leaf node. The leaf node represents the individual operations performed by the database engine to execute the query, such as table scans or index lookups.
• Follow from Leaf to Root: After understanding the leaf node, follow the execution flow from the inner node to the outer node. The child nodes are directly below the root node and represent the different steps taken by the database engine to execute the query.
• Multiple Child Nodes: In some cases, there might be multiple child nodes that can be identified by statements on the same level. Each step in the execution process will have its individual cost.

To gain a better understanding, let's explain with a complex query and see how the Explain Tree format displays the execution order.

Query:

 
EXPLAIN format=tree SELECT users.display_name, COUNT(*) AS num_edits FROM users LEFT JOIN changesets ON users.id = changesets.user_id GROUP BY users.display_name ORDER BY num_edits DESC LIMIT 10;

+----------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Limit: 10 row(s)
    -> Sort: num_edits DESC, limit input to 10 row(s) per chunk
        -> Table scan on 
            -> Aggregate using temporary table
                -> Left hash join (changesets.user_id = users.id)  (cost=27.3e+6 rows=272e+6)
                    -> Index scan on users using users_display_name_idx  (cost=482 rows=3855)
                    -> Hash
                        -> Table scan on changesets  (cost=2.97 rows=70686)
 |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
	

Visualization of the tree

Let's analyze the previous example of the Explain Tree format to understand the execution order of the query:

• Table Scan on Leaf Node: In the example, the table scan on c is represented as the leaf node. This indicates that the table scan was executed first in the query execution process.
• Reading from Leaf to Parent Node: As mentioned earlier, the next step is to read from the leaf node towards the parent node, which is the node outside. In this case, there are two nodes on the same level (the table scan and the aggregate using a temporary table). The individual costs of these nodes will be considered in the execution process.
• Easy Understanding from Inside to Outside: By following the execution flow from inside to outside, we can easily grasp the sequence of operations. The limit and sort operations are typically executed last in most queries. As a result, they are located at the top of the Explain Tree format.

Harnessing the Power of Explain Plan

These are some scenarios in which to use the Explain plan.

• Explain plan is recommended in situations where you are uncertain about the query's optimal performance
• To check whether the query uses indexes efficiently or proceeds to a full table scan.
• To check whether the index is working and to discard unnecessary indexes.
• To check the row count instead of using count(), explain may return the rows present in the table.
• To find the cause of a slow-running query and to optimize it.

Maximizing Query Performance and Troubleshooting with Explain Plan

Benefits:

• To achieve optimal query performance, understand the query execution plan and optimize queries accordingly.
• To identify and address performance bottlenecks, analyze the output to detect full table scans, inefficient join types, or suboptimal index usage.
• To minimize the risk of syntax errors or typos, review the Explain plan before executing queries.
• To diagnose issues, examine the operations performed and data filtering/sorting through the Explain plan.

Understanding Limitations and Considerations

1. Estimated row counts may not always be accurate and can be affected by factors such as data distribution, statistics accuracy, and query complexity.
2. Lack of real-time data due to neglecting real-time factors such as server load, concurrency, and disk I/O performance may result in not reflecting the actual performance of the query at runtime.
3. The order in which MySQL joins tables can have a significant impact on query performance, but the EXPLAIN statement does not always reveal the join order used by the optimizer.

Hence, you can leverage the power of Explain Plan to fine-tune your database queries, optimize indexes, and identify performance bottlenecks.

If you found this particular blog post helpful, chances are you will find our other blogs that capture your interest and provide valuable insights and advice for your business or career. And if you require expert assistance in managing your database, do not hesitate to contact us today.