MySQL 8.3 introduced the explain_json_format_version system variable that allows us to specify which JSON output format should be used when we use the EXPLAIN statement with FORMAT=JSON.
It also applies when we omit the FORMAT argument and the explain_format system variable is set to JSON (because explain_format specifies the default format to be used by EXPLAIN).
The output of the EXPLAIN statement can be in one of three formats: TRADITIONAL (or its synonym DEFAULT), TREE, or JSON. The explain_json_format_version variable is only relevant when the output format is JSON.
Currently explain_json_format_version can be set to either 1 or 2.
Set the explain_json_format_version Value
We can set the explain_json_format_version variable like this:
SET explain_json_format_version = 1;Result:
Query OK, 0 rows affected (0.01 sec)
That’s all that’s needed. The variable is now set to 1 (which happens to be the default value anyway).
Get the explain_json_format_version Value
We can get the current value of the explain_json_format_version system variable by selecting @@explain_json_format_version. Like this:
SELECT @@explain_json_format_version;Result:
+-------------------------------+ | @@explain_json_format_version | +-------------------------------+ | 1 | +-------------------------------+
So we can see that it’s set to 1, which is the default value for this system variable.
Valid Values
As of this writing, we can set the explain_json_format_version variable to either 1 or 2.
As mentioned, 1 is the default value.
Here’s what the MySQL documentation says about the JSON format 2:
this JSON output format is based on access paths, and is intended to provide better compatibility with future versions of the MySQL Optimizer.
Example of Usage
Here’s a simple EXPLAIN statement using FORMAT=JSON:
SET explain_json_format_version = 1;
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"
]
}
}
]
}
}
The results are in JSON format as expected.
But more to the point, this is JSON format 1. I made sure of that by starting off with SET explain_json_format_version = 1;.
Change explain_json_format_version and Run it Again
Now let’s set my explain_json_format_version variable to 2 and run the statement again:
SET explain_json_format_version = 2;
EXPLAIN FORMAT=JSON
SELECT * FROM Artists a
JOIN Albums al ON a.ArtistId = al.ArtistId;Result:
{
"query": "/* select#1 */ select `music`.`a`.`ArtistId` AS `ArtistId`,`music`.`a`.`ArtistName` AS `ArtistName`,`music`.`a`.`ActiveFrom` AS `ActiveFrom`,`music`.`al`.`AlbumId` AS `AlbumId`,`music`.`al`.`AlbumName` AS `AlbumName`,`music`.`al`.`ReleaseDate` AS `ReleaseDate`,`music`.`al`.`ArtistId` AS `ArtistId`,`music`.`al`.`GenreId` AS `GenreId` from `music`.`artists` `a` join `music`.`albums` `al` where (`music`.`a`.`ArtistId` = `music`.`al`.`ArtistId`)",
"inputs": [
{
"alias": "al",
"operation": "Table scan on al",
"table_name": "albums",
"access_type": "table",
"schema_name": "music",
"used_columns": [
"AlbumId",
"AlbumName",
"ReleaseDate",
"ArtistId",
"GenreId"
],
"estimated_rows": 20.0,
"estimated_total_cost": 2.25
},
{
"alias": "a",
"covering": false,
"operation": "Single-row index lookup on a using PRIMARY (ArtistId=al.ArtistId)",
"index_name": "PRIMARY",
"table_name": "artists",
"access_type": "index",
"schema_name": "music",
"used_columns": [
"ArtistId",
"ArtistName",
"ActiveFrom"
],
"estimated_rows": 1.0,
"lookup_condition": "ArtistId=al.ArtistId",
"index_access_type": "index_lookup",
"estimated_total_cost": 0.255
}
],
"join_type": "inner join",
"operation": "Nested loop inner join",
"access_type": "join",
"estimated_rows": 20.0,
"join_algorithm": "nested_loop",
"estimated_total_cost": 9.25
}
The results, although still in JSON, look a bit different. That’s because it’s using JSON format 2.
Using the explain_format Variable
The FORMAT argument is optional when using the EXPLAIN statement. If we omit this argument then the explain_format system variable is used to provide the output format.
As alluded to, the explain_json_format_version system variable only applies when the output format is JSON.
So let’s set explain_format and explain_json_format_version, then run an EXPLAIN statement without the FORMAT argument:
SET explain_format = JSON;
SET explain_json_format_version = 2;
EXPLAIN SELECT * FROM Artists;Result:
{
"query": "/* select#1 */ select `music`.`artists`.`ArtistId` AS `ArtistId`,`music`.`artists`.`ArtistName` AS `ArtistName`,`music`.`artists`.`ActiveFrom` AS `ActiveFrom` from `music`.`artists`",
"operation": "Table scan on Artists",
"table_name": "Artists",
"access_type": "table",
"schema_name": "music",
"used_columns": [
"ArtistId",
"ArtistName",
"ActiveFrom"
],
"estimated_rows": 16.0,
"estimated_total_cost": 1.85
}
I used a shorter SQL statement here in order to reduce the amount output. Either way, we can see that the explain_json_format_version system variable applies to the JSON output even when JSON is the default as specified in the explain_format variable.
When explain_json_format_version is Irrelevant
Given the explain_json_format_version variable only applies to JSON output, it becomes irrelevant whenever we use non-JSON output.
For example, if the output is TRADITIONAL then explain_json_format_version has no effect:
EXPLAIN FORMAT=TRADITIONAL
SELECT * FROM Artists;Result:
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+ | 1 | SIMPLE | Artists | NULL | ALL | NULL | NULL | NULL | NULL | 16 | 100.00 | NULL | +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
This is the same whether we explicitly specify FORMAT=TRADITIONAL or simply use the default value provided by explain_format:
SET explain_format = TRADITIONAL;
SET explain_json_format_version = 2;
EXPLAIN SELECT * FROM Artists;Result:
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+ | 1 | SIMPLE | Artists | NULL | ALL | NULL | NULL | NULL | NULL | 16 | 100.00 | NULL | +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
The output is not in JSON and so explain_json_format_version has no relevance in such cases.