MySQL 8.0.32 introduced the explain_format system variable, which can be used to provide a default output format when using the EXPLAIN statement, or its synonyms DESCRIBE and DESC.
More specifically, it determines the default output format used by this statement in the absence of a FORMAT option when displaying a query execution plan.
Get the explain_format Value
We can get the current value of the explain_format system variable by selecting @@explain_format. Like this:
SELECT @@explain_format;Result:
+------------------+ | @@explain_format | +------------------+ | TRADITIONAL | +------------------+
In my case it’s set to TRADITIONAL, which is the default value for this system variable.
Set the explain_format Value
We can set the explain_format variable to a different value like this:
SET explain_format = JSON;Result:
Query OK, 0 rows affected (0.00 sec)
Now let’s get the value again:
SELECT @@explain_format;Result:
+------------------+ | @@explain_format | +------------------+ | JSON | +------------------+
It now reflects the new value.
Valid Values
We can set the explain_format variable to either TREE, JSON, TRADITIONAL or DEFAULT (which is a synonym for TRADITIONAL).
Setting it to DEFAULT has the same effect as specifying TRADITIONAL – using it will cause the explain_format variable to be set to TRADITIONAL.
Example of Usage
The explain_format system variable provides the default output format when we run a statement like this:
EXPLAIN
SELECT * FROM Artists a
JOIN Albums al ON a.ArtistId = al.ArtistId;Result:
+----+-------------+-------+------------+--------+-------------------+---------+---------+-------------------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+--------+-------------------+---------+---------+-------------------+------+----------+-------+ | 1 | SIMPLE | al | NULL | ALL | FK_Albums_Artists | NULL | NULL | NULL | 20 | 100.00 | NULL | | 1 | SIMPLE | a | NULL | eq_ref | PRIMARY | PRIMARY | 4 | music.al.ArtistId | 1 | 100.00 | NULL | +----+-------------+-------+------------+--------+-------------------+---------+---------+-------------------+------+----------+-------+
In this case I didn’t specify the output format in my EXPLAIN statement, and so the format is taken from my explain_format system variable. In this case my explain_format variable was set to TRADITIONAL, and so that’s what we got.
Change explain_format and Run it Again
Let’s change the explain_format value:
SET explain_format=TREE;Now let’s run the statement again:
EXPLAIN
SELECT * FROM Artists a
JOIN Albums al ON a.ArtistId = al.ArtistId;Result:
-> Nested loop inner join (cost=9.25 rows=20)
-> Table scan on al (cost=2.25 rows=20)
-> Single-row index lookup on a using PRIMARY (ArtistId=al.ArtistId) (cost=0.255 rows=1)
This time the output uses the TREE format, as expected.
Override the explain_format Variable
We can override the explain_format variable by explicitly setting the format in the EXPLAIN statement:
EXPLAIN FORMAT=JSON
SELECT * FROM Artists a
JOIN Albums al ON a.ArtistId = al.ArtistId;Result:
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "9.25"
},
"nested_loop": [
{
"table": {
"table_name": "al",
"access_type": "ALL",
"possible_keys": [
"FK_Albums_Artists"
],
"rows_examined_per_scan": 20,
"rows_produced_per_join": 20,
"filtered": "100.00",
"cost_info": {
"read_cost": "0.25",
"eval_cost": "2.00",
"prefix_cost": "2.25",
"data_read_per_join": "15K"
},
"used_columns": [
"AlbumId",
"AlbumName",
"ReleaseDate",
"ArtistId",
"GenreId"
]
}
},
{
"table": {
"table_name": "a",
"access_type": "eq_ref",
"possible_keys": [
"PRIMARY"
],
"key": "PRIMARY",
"used_key_parts": [
"ArtistId"
],
"key_length": "4",
"ref": [
"music.al.ArtistId"
],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 20,
"filtered": "100.00",
"cost_info": {
"read_cost": "5.00",
"eval_cost": "2.00",
"prefix_cost": "9.25",
"data_read_per_join": "15K"
},
"used_columns": [
"ArtistId",
"ArtistName",
"ActiveFrom"
]
}
}
]
}
}
When explain_format is Ignored
There are some cases where the explain_format value is ignored. Well kind of.
When we use EXPLAIN ANALYZE only the TREE output format is supported. Because of this, TREE becomes the default output format whenever we use EXPLAIN ANALYZE. This was the case even before the explain_format variable was introduced.
But when explain_format was introduced, it came with a default value of TRADITIONAL (perhaps because TRADITIONAL is still the default format for EXPLAIN without ANALYZE). So basically we have a bit of a conflict!
Because of this, MySQL has a slightly nuanced way of dealing with the explain_format value when using EXPLAIN ANALYZE. It goes like this:
- If the value of the
explain_formatsystem variable isTREEorTRADITIONAL(or its synonymDEFAULT), thenEXPLAIN ANALYZEuses theTREEformat. - If the value of
explain_formatisJSON, thenEXPLAIN ANALYZEreturns an error unlessFORMAT=TREEis explicitly specified as part of theEXPLAIN ANALYZEstatement.
Basically, if your explain_format is set to JSON, then you should override that with FORMAT=TREE in your EXPLAIN ANALYZE statement. Either that or set your explain_format to either TREE or TRADITIONAL (or use DEFAULT to set it to TRADITIONAL).
So if your explain_format is set to TREE or TRADITIONAL you have nothing to worry about, and can use EXPLAIN ANALYZE without explicitly specifying the TREE format in the statement.
Example
To demonstrate what I mean by the above explanation, let’s look at an example.
Let’s check our explain_format system variable:
SELECT @@explain_format;Result:
+------------------+ | @@explain_format | +------------------+ | TRADITIONAL | +------------------+
So it’s set to TRADITIONAL.
Now let’s run EXPLAIN ANALYZE:
EXPLAIN ANALYZE
SELECT * FROM actor a
JOIN film_actor fa
ON a.actor_id = fa.actor_id
JOIN film f
ON f.film_id = fa.film_id
WHERE last_name LIKE 'Pit%';Result:
-> Nested loop inner join (cost=15.8 rows=27.3) (actual time=0.894..1.64 rows=31 loops=1)
-> Nested loop inner join (cost=4.39 rows=27.3) (actual time=0.835..0.882 rows=31 loops=1)
-> Index range scan on a using idx_actor_last_name over ('Pit' <= last_name <= 'Pit????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????'), with index condition: (a.last_name like 'Pit%') (cost=0.71 rows=1) (actual time=0.752..0.778 rows=1 loops=1)
-> Index lookup on fa using PRIMARY (actor_id=a.actor_id) (cost=3.68 rows=27.3) (actual time=0.0817..0.0986 rows=31 loops=1)
-> Single-row index lookup on f using PRIMARY (film_id=fa.film_id) (cost=0.322 rows=1) (actual time=0.0241..0.0242 rows=1 loops=31)
As expected, the output actually uses the TREE format, even though our explain_format variable is set to TRADITIONAL.
This is in line with the rule mentioned above:
- If the value of the
explain_formatsystem variable isTREEorTRADITIONAL(or its synonymDEFAULT), thenEXPLAIN ANALYZEuses theTREEformat.
But this doesn’t mean we can use TRADITIONAL or DEFAULT in the EXPLAIN ANALYZE statement itself. No. If we do that, we’ll get an error:
EXPLAIN ANALYZE FORMAT=TRADITIONAL
SELECT * FROM actor a
JOIN film_actor fa
ON a.actor_id = fa.actor_id
JOIN film f
ON f.film_id = fa.film_id
WHERE last_name LIKE 'Pit%';Result:
ERROR 1235 (42000): This version of MySQL doesn't yet support 'EXPLAIN ANALYZE with TRADITIONAL format'
So we get two different treatments for the TRADITIONAL output format, depending on whether it’s coming from our explain_format system variable or specified explicitly in the EXPLAIN ANALYZE statement.
Why Does it Work This Way?
Basically, it works this way for backward compatibility reasons. The EXPLAIN statement with its FORMAT argument was released long before the EXPLAIN ANALYZE option was released, which itself was released in an earlier MySQL version than the explain_format system variable.
By having TRADITIONAL automatically translated into TREE (when it’s specified in explain_format), this ensures that the EXPLAIN ANALYZE statement continues to use the TREE format by default, as it did prior to the introduction of explain_format.
This makes sense when you think about it. Because if this wasn’t the case, then the default behaviour of EXPLAIN ANALYZE would be an error on any system that uses the default value (of TRADITIONAL) in explain_format. In such a case, the only way we’d be able to get EXPLAIN ANALYZE to work would be to either explicitly use FORMAT=TREE or to change our explain_format to TREE.
And of course, changing explain_format to TREE could break other applications that rely on the previous default of TRADITIONAL.