Imagine a world where you can peek behind the scenes, observing precisely how your database processes your queries, choosing which tables to scan and the order in which it retrieves rows. This level of insight into the inner workings of MySQL can be achieved through the powerful Explain tool.

Check out our recent blog Demystifying MySQL Explain Tree Format for a deeper understanding of query optimization.

In this blog, we embark on a journey through the intriguing world of MySQL Explain Format. We'll uncover its various facets, explore the different formats it offers, and gain the knowledge needed to set your preferred format as the default. Along the way, we'll highlight the best use cases for each format, ensuring that you're equipped to tackle real-world database challenges effectively.

The explain keyword is a powerful tool that can help you understand the execution plans of your queries. However, not all queries are the same, and preferences in choosing the type of format can vary. The problem is that if you want to use the TREE format as the default, you have to specify the FORMAT=TREE option every time you run the EXPLAIN statement. This can be tedious and error-prone.

Before MySQL 8.0.32, the explain keyword only supported the traditional format as the default. However, in MySQL 8.0.32, a new system variable called explain_format was introduced. This variable allows you to specify the default format for the explain plan.

The Three Formats Demystified

Before exploring the variable, let's check out the types of formats available.

The three different formats for the explain plan are:

• Traditional: The traditional format is the default. It is a table-based format that shows the different steps involved in the execution of the query.
• JSON: The JSON output format of the EXPLAIN statement in MySQL is a more compact and informative way to represent the query plan, including information about the JSON data types used.
• TREE: The TREE format is a graphical representation of the explain plan. It is a tree-like structure that shows the different steps involved in the execution of the query.

Choosing the right format

Traditional Format (Default)

• Suitable For: Simple queries, subqueries, and queries involving joins.
• Description: This format provides a tabular view of the execution plan, making it exceptionally easy to read and understand.
• Ideal for: Most users who prefer a clear, straightforward representation of the query plan.

JSON Format

• Suitable For: Subqueries and queries with joins requiring programmatic processing.
• Description: JSON format offers a more compact and structured view of the execution plan, simplifying parsing and analysis.
• Ideal for: Developers who need to optimize queries or troubleshoot performance issues programmatically.

Tree Format

• Suitable For: Queries with joins that benefit from visualization.
• Description: The Tree format provides a visual representation of the execution plan, offering a high-level overview of how the query unfolds.
• Ideal for: Users seeking a graphical understanding of query execution, especially beneficial for complex join scenarios.

Flexibility and Compatibility

• Users enjoy the flexibility to select the format that aligns with their preferences and the query at hand.
• It's important to note that a specific format isn't limited to handling only a particular query type.
• Furthermore, MySQL version 8.0.32 introduces a helpful feature: the explain_format system variable, simplifying the process of switching between different formats.
• This enhancement enhances the overall user experience, making it more convenient to adapt to varying query optimization needs.

‍

EXPLAIN_FORMAT

The explain_format variable is a useful tool for understanding the execution plans of your queries. By using the different formats, we can get a better understanding of how MySQL is planning to execute your queries and identify any potential performance bottlenecks.

For instance, consider a scenario where you're using the traditional format and notice a significantly high value in the Rows column. This can serve as an early warning sign that your query may be scanning an excessive number of rows, potentially impacting performance. In response, you can employ the explain_format variable to swiftly switch to the JSON format. This shift enables you to investigate which indexes could be harnessed to enhance your query's performance.

A key point to note is that the explain_format variable made its debut in MySQL 8.0.32. This feature offers both Global and Session scopes, providing flexibility in how you manage and fine-tune query analysis.

Setting EXPLAIN_FORMAT

Given that the explain_format is a versatile variable, you have the freedom to select the format that best suits your query plan exploration needs.

Here's how you can seamlessly switch between formats:

For Session-Specific Format:

• If you wish to modify the format only for the current session, you can do so by employing the following commands:

 
Set session explain_format=TREE;  or  Set explain_format=TREE;
	

• These commands allow you to tailor the format for your current session without impacting other users.

For Global Format Setting (Affecting All Sessions):

• If your intention is to establish a format globally, affecting all sessions, you can use the following command:

 
 Set global explain_format=JSON;
	

• However, it's crucial to note that when implementing global changes, you must log in to the MySQL server once more to ensure that the altered format takes effect across all sessions.

Now, let's experiment with a complex query, one that features a subquery. In this exploration, we will leverage both the TREE and JSON formats to gain deeper insights into the query execution process and its outcomes.

‍Example:

 
Set explain_format=TREE;
	

Output:

 
Explain SELECT w.*, n.* FROM ways w JOIN nodes n ON w.way_id = n.node_id WHERE (w.way_id, w.version) IN (
    SELECT way_id, MAX(version) FROM ways GROUP BY way_id) \G

*************************** 1. row ***************************

EXPLAIN: -> Nested loop inner join  (cost=578370 rows=482976)
    -> Filter: ((w.way_id,w.version),(w.way_id,w.version) in (select #2))  (cost=50126 rows=482976)
        -> Table scan on w  (cost=50126 rows=482976)
        -> Select #2 (subquery in condition; run only once)
            -> Filter: ((w.way_id = ``.way_id) and (w.version = ``.`MAX(version)`))  (cost=146721..146721 rows=1)
                -> Limit: 1 row(s)  (cost=146721..146721 rows=1)
                    -> Index lookup on  using  (way_id=w.way_id, MAX(version)=w.version)
                        -> Materialize with deduplication  (cost=146721..146721 rows=482976)
                            -> Group aggregate: max(ways.version)  (cost=98423 rows=482976)
                                -> Index scan on ways using PRIMARY  (cost=50126 rows=482976)
    -> Index lookup on n using PRIMARY (node_id=w.way_id)  (cost=0.994 rows=1)
	

Example:

 
Set explain_format=JSON;
	

Output:

 
Explain SELECT w.*, n.* FROM ways w JOIN nodes n ON w.way_id = n.node_id WHERE (w.way_id, w.version) IN (
    SELECT way_id, MAX(version)FROM ways GROUP BY way_id) \G
*************************** 1. row ***************************
EXPLAIN: {
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "581399.20"
    },
    "nested_loop": [
      {
        "table": {
          "table_name": "w",
          "access_type": "ALL",
          "possible_keys": [
            "PRIMARY"
          ],
          "rows_examined_per_scan": 482976,
          "rows_produced_per_join": 482976,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "1828.00",
            "eval_cost": "48297.60",
            "prefix_cost": "50125.60",
            "data_read_per_join": "14M"
          },
           "used_columns": [
            "way_id",
            "changeset_id",
            "timestamp",
            "version",
            "visible"
          ],
          "attached_condition": 
        "((`osm`.`w`.`way_id`,`osm`.`w`.`version`),(`osm`.`w`.`way_id`,`osm`.`w`.`version`) in (  (/* select#2 */ select `osm`.`ways`.`way_id`,max(`osm`.`ways`.`version`) from `osm`.`ways` group by `osm`.`ways`.`way_id` having true ), (`osm`.`w`.`way_id` in  on  where ((`osm`.`w`.`way_id` = ``.`way_id`) and (`osm`.`w`.`version` = ``.`MAX(version)`)))))",
          "attached_subqueries": [
              {
              "table": {
                "table_name": "",
                "access_type": "eq_ref",
                "key": "",
                "key_length": "17",
                "rows_examined_per_scan": 1,
                "materialized_from_subquery": {
                  "using_temporary_table": true,
                  "dependent": true,
                  "cacheable": false,
                  "query_block": {
                    "select_id": 2,
                    "cost_info": {
                      "query_cost": "50125.60"
                    },
                     "grouping_operation": {
                      "using_filesort": false,
                      "table": {
                        "table_name": "ways",
                        "access_type": "index",
                        "possible_keys": [
                          "PRIMARY",
                          "changeset_id",
                          "version_time_idx"
                        ],
                        "key": "PRIMARY",
                        "used_key_parts": [
                          "way_id",
                          "version"
                        ],
                        "key_length": "16",
                        "rows_examined_per_scan": 482976,
                        "rows_produced_per_join": 482976,
                        "filtered": "100.00",
                        "using_index": true,
                        "cost_info": {
                          "read_cost": "1828.00",
                          "eval_cost": "48297.60",
                          "prefix_cost": "50125.60",
                          "data_read_per_join": "14M"
                        },
                        "used_columns": [
                          "way_id",
                          "version"
                        ]
                      }
                    }
                  }
                }
              }
            }
          ]
        }
      },
      {
        "table": {
          "table_name": "n",
          "access_type": "ref",
          "possible_keys": [
            "PRIMARY"
          ],
          "key": "PRIMARY",
          "used_key_parts": [
            "node_id"
          ],
          "key_length": "8",
          "ref": [
            "osm.w.way_id"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 482976,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "482976.00",
            "eval_cost": "48297.60",
            "prefix_cost": "581399.20",
            "data_read_per_join": "22M"
          },
          "used_columns": [
            "node_id",
            "latitude",
            "longitude",
            "changeset_id",
            "visible",
            "timestamp",
            "tile",
            "version"
          ]
        }
      }
    ]
  }
}
	

To establish a default format for MySQL Explain, you have the option to configure the explain_format variable within the my.cnf file. This involves adding the variable and specifying your desired format. However, it's important to note that unless you set the variable as session-wise within the command prompt, the format specified in the my.cnf file will not be altered.

The significant advantage of using this approach is evident in the example you've provided. When you set the variable to TREE format in the my.cnf file, you consistently receive explain plan results in the TREE format. Likewise, if you configure it as JSON, you consistently obtain explain plan results in JSON format. This predictability streamlines the query analysis process, ensuring that you consistently receive results in your preferred format.

Limitations

It's worth noting an interesting behavior related to the explain_format variable: when the format is set to TRADITIONAL, it will be interpreted as TREE when you run EXPLAIN ANALYZE. This behavior can be illustrated with an example.

Example:

Consider the scenario where the explain_format variable is configured as TRADITIONAL. Now, let's proceed to run EXPLAIN ANALYZE for further insight.

This behavior essentially means that when you intend to use the TRADITIONAL format with EXPLAIN ANALYZE, you'll actually receive the results in the TREE format. It's an exception to keep in mind when analyzing query plans under specific conditions.

 
show variables like '%explain_format%';
+----------------+-------------+
| Variable_name  | Value       |
+----------------+-------------+
| explain_format | TRADITIONAL |
+----------------+-------------+
	

Output:

 
mysql> explain analyze table nodes;
+------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                              |
+------------------------------------------------------------------------------------------------------+
| -> Table scan on nodes  (cost=298603 rows=2.85e+6) (actual time=0.0975..16031 rows=2.87e+6 loops=1)
 |
+------------------------------------------------------------------------------------------------------+
	

• The result is in TREE format even though the explain_format is in TRADITIONAL this is one of the exceptions of the explain_format variable.
• When the explain_format is configured as JSON and  EXPLAIN ANALYZE is executed, this combination leads to an error, To better understand this scenario, let's delve into an example.Example:Here the explain_format is set to JSON.

Example:

 
show variables like '%explain_format%';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| explain_format | JSON  |
+----------------+-------+
	

Output:

 
mysql> explain analyze table nodes;
ERROR 1235 (42000): This version of MySQL doesn't yet support 'EXPLAIN ANALYZE with JSON format'
	

• When using a format specifier alongside EXPLAIN or EXPLAIN ANALYZE, any existing setting for explain_format will be overridden. In other words, if you specify a format specifier, like FORMAT=TRADITIONAL, the explain_format system variable will be disregarded.

• The explain_format system variable is a session variable. This means that it only affects the current session. If you want to change the explain_format system variable for all sessions, you need to set it on the global level. To do this, you need to have the SYSTEM_VARIABLES_ADMIN privilege (or the deprecated SUPER privilege). The flow chart below summarises the points regarding the session-level setting and the use of format specifier.

The flow chart below summarises the points regarding the session-level setting and the use of format specifier.

MySQL's explain_format variable empowers users to customize how they analyze query execution plans, offering greater flexibility and convenience. With options like TRADITIONAL, JSON, and TREE, users can choose the format that best aligns with their specific needs and preferences. However, it's essential to be aware of exceptions, such as TRADITIONAL being interpreted as TREE in EXPLAIN ANALYZE.Additionally, the dynamic nature of the explain_format variable allows for easy and efficient adjustments, with the ability to set it at both the session and global levels. While it greatly enhances query analysis, users should also be cautious of format specifier overrides when using EXPLAIN or EXPLAIN ANALYZE.References

• Reference 1
• Reference 2

Stay connected with Mydbops blogs for more technical insights and discoveries in the MySQL ecosystem.