In MySQL we can use the EXPLAIN statement to get information about SQL queries that we run against the database. We can also get information about tables and views.
EXPLAIN is a synonym for DESCRIBE (and its short form DESC). Even though these statements all work exactly the same, there’s a common convention amongst MySQL developers to use DESCRIBE for certain tasks and EXPLAIN for others. Typically, DESCRIBE is often used to get information about tables and views, whereas EXPLAIN is used to get information about queries, such as query execution plans.
Example
To start off simple, let’s use EXPLAIN to get information about a view in the sakila sample database:
EXPLAIN actor_info;Result:
+------------+-------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------------+------+-----+---------+-------+ | actor_id | smallint unsigned | NO | | 0 | | | first_name | varchar(45) | NO | | NULL | | | last_name | varchar(45) | NO | | NULL | | | film_info | text | YES | | NULL | | +------------+-------------------+------+-----+---------+-------+
This returns information about the columns in the view. We can see the names of each column in the Field column, their respective data types, whether or not they’re nullable, and more. The Extra column can tell us things like if the column is a generated column, whether it’s an invisible column, and more.
Get the Query Execution Plan
The EXPLAIN statement is often used to get information about queries, such as a query execution plan that shows how MySQL executes the SQL statement behind the scenes.
We can do this for SELECT, DELETE, INSERT, REPLACE, TABLE, and UPDATE statements.
Here’s what happens when we select all columns and rows from the above view:
EXPLAIN SELECT * FROM actor_info;Result:
+----+--------------------+------------+------------+--------+-----------------------------------+---------+---------+----------------------------------------+------+----------+----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------------+------------+------------+--------+-----------------------------------+---------+---------+----------------------------------------+------+----------+----------------+ | 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 5461 | 100.00 | NULL | | 2 | DERIVED | a | NULL | ALL | NULL | NULL | NULL | NULL | 200 | 100.00 | Using filesort | | 2 | DERIVED | fa | NULL | ref | PRIMARY | PRIMARY | 2 | sakila.a.actor_id | 27 | 100.00 | Using index | | 2 | DERIVED | fc | NULL | ref | PRIMARY | PRIMARY | 2 | sakila.fa.film_id | 1 | 100.00 | Using index | | 2 | DERIVED | c | NULL | eq_ref | PRIMARY | PRIMARY | 1 | sakila.fc.category_id | 1 | 100.00 | NULL | | 3 | DEPENDENT SUBQUERY | fa | NULL | ref | PRIMARY,idx_fk_film_id | PRIMARY | 2 | sakila.a.actor_id | 27 | 100.00 | Using index | | 3 | DEPENDENT SUBQUERY | f | NULL | eq_ref | PRIMARY | PRIMARY | 2 | sakila.fa.film_id | 1 | 100.00 | NULL | | 3 | DEPENDENT SUBQUERY | fc | NULL | eq_ref | PRIMARY,fk_film_category_category | PRIMARY | 3 | sakila.fa.film_id,sakila.c.category_id | 1 | 100.00 | Using index | +----+--------------------+------------+------------+--------+-----------------------------------+---------+---------+----------------------------------------+------+----------+----------------+ 8 rows in set, 3 warnings (0.00 sec)
We can see that this view selects data from multiple tables, we can see whether or not it uses an index in the process, and much more.
We can also see that there are three warnings in this case. Warnings provide additional execution plan information.
Let’s check the warnings:
SHOW WARNINGS;Result:
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Note | 1276 | Field or reference 'sakila.c.category_id' of SELECT #3 was resolved in SELECT #2 | | Note | 1276 | Field or reference 'sakila.a.actor_id' of SELECT #3 was resolved in SELECT #2 | | Note | 1003 | /* select#1 */ select `sakila`.`actor_info`.`actor_id` AS `actor_id`,`sakila`.`actor_info`.`first_name` AS `first_name`,`sakila`.`actor_info`.`last_name` AS `last_name`,`sakila`.`actor_info`.`film_info` AS `film_info` from `sakila`.`actor_info` | +-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
We can get the same result by copying and pasting the query from the view:
EXPLAIN
SELECT
`a`.`actor_id` AS `actor_id`,
`a`.`first_name` AS `first_name`,
`a`.`last_name` AS `last_name`,
GROUP_CONCAT(DISTINCT CONCAT(`c`.`name`,': ',
(SELECT group_concat(`f`.`title` ORDER BY `f`.`title` ASC separator ', ')
FROM ((`film` `f` JOIN `film_category` `fc` ON((`f`.`film_id` = `fc`.`film_id`))) JOIN `film_actor` `fa` ON((`f`.`film_id` = `fa`.`film_id`)))
WHERE ((`fc`.`category_id` = `c`.`category_id`) AND (`fa`.`actor_id` = `a`.`actor_id`))))
ORDER BY `c`.`name` ASC separator '; ') AS `film_info` FROM (((`actor` `a` LEFT JOIN `film_actor` `fa` ON((`a`.`actor_id` = `fa`.`actor_id`))) LEFT JOIN `film_category` `fc` ON((`fa`.`film_id` = `fc`.`film_id`))) LEFT JOIN `category` `c` ON((`fc`.`category_id` = `c`.`category_id`)))
GROUP BY `a`.`actor_id`,`a`.`first_name`,`a`.`last_name`;Result:
+----+--------------------+-------+------------+--------+-----------------------------------+---------+---------+----------------------------------------+------+----------+----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------------+-------+------------+--------+-----------------------------------+---------+---------+----------------------------------------+------+----------+----------------+ | 1 | PRIMARY | a | NULL | ALL | NULL | NULL | NULL | NULL | 200 | 100.00 | Using filesort | | 1 | PRIMARY | fa | NULL | ref | PRIMARY | PRIMARY | 2 | sakila.a.actor_id | 27 | 100.00 | Using index | | 1 | PRIMARY | fc | NULL | ref | PRIMARY | PRIMARY | 2 | sakila.fa.film_id | 1 | 100.00 | Using index | | 1 | PRIMARY | c | NULL | eq_ref | PRIMARY | PRIMARY | 1 | sakila.fc.category_id | 1 | 100.00 | NULL | | 2 | DEPENDENT SUBQUERY | fa | NULL | ref | PRIMARY,idx_fk_film_id | PRIMARY | 2 | sakila.a.actor_id | 27 | 100.00 | Using index | | 2 | DEPENDENT SUBQUERY | f | NULL | eq_ref | PRIMARY | PRIMARY | 2 | sakila.fa.film_id | 1 | 100.00 | NULL | | 2 | DEPENDENT SUBQUERY | fc | NULL | eq_ref | PRIMARY,fk_film_category_category | PRIMARY | 3 | sakila.fa.film_id,sakila.c.category_id | 1 | 100.00 | Using index | +----+--------------------+-------+------------+--------+-----------------------------------+---------+---------+----------------------------------------+------+----------+----------------+ 7 rows in set, 3 warnings (0.00 sec)
Specify the Format
We can use the FORMAT argument to specify the output format. Supported formats are TRADITIONAL, JSON, and TREE.
Here’s an example of specifying JSON:
EXPLAIN FORMAT=JSON SELECT * FROM actor;Result:
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "20.25"
},
"table": {
"table_name": "actor",
"access_type": "ALL",
"rows_examined_per_scan": 200,
"rows_produced_per_join": 200,
"filtered": "100.00",
"cost_info": {
"read_cost": "0.25",
"eval_cost": "20.00",
"prefix_cost": "20.25",
"data_read_per_join": "73K"
},
"used_columns": [
"actor_id",
"first_name",
"last_name",
"last_update"
]
}
}
}
In this case the query selected all rows from a table called actor.
The default format is TRADITIONAL, but this will depend on your explain_format system variable if present (this variable was introduced in MySQL 8.0.32). If this variable is set to a value other than TRADITIONAL, then the default format will be whatever that value is (assuming it’s a valid value).
We can check the value of the explain_format system variable like this:
SELECT @@explain_format;Result:
+------------------+ | @@explain_format | +------------------+ | TRADITIONAL | +------------------+
In my case it’s set to the default value of TRADITIONAL.
Using EXPLAIN ANALYZE
From MySQL 8.0.18 we can use EXPLAIN ANALYZE to get EXPLAIN output along with timing and additional, iterator-based, information about how the optimiser’s expectations matched the actual execution.
We can use EXPLAIN ANALYZE on SELECT and TABLE statements, as well as with multi-table UPDATE and DELETE statements.
Example:
EXPLAIN ANALYZE
SELECT
`albums`.`AlbumName` AS `AlbumName`,
`artists`.`ArtistName` AS `ArtistName`,
`albums`.`ReleaseDate` AS `ReleaseDate`
FROM ((`albums`
JOIN `artists` ON((`albums`.`ArtistId` = `artists`.`ArtistId`)))
JOIN `genres` ON((`albums`.`GenreId` = `genres`.`GenreId`)));Result:
-> Nested loop inner join (cost=16.2 rows=20) (actual time=0.113..0.209 rows=20 loops=1)
-> Nested loop inner join (cost=9.25 rows=20) (actual time=0.102..0.156 rows=20 loops=1)
-> Filter: (albums.GenreId is not null) (cost=2.25 rows=20) (actual time=0.0773..0.0982 rows=20 loops=1)
-> Table scan on albums (cost=2.25 rows=20) (actual time=0.0757..0.0932 rows=20 loops=1)
-> Single-row covering index lookup on genres using PRIMARY (GenreId=albums.GenreId) (cost=0.255 rows=1) (actual time=0.00233..0.00241 rows=1 loops=20)
-> Single-row index lookup on artists using PRIMARY (ArtistId=albums.ArtistId) (cost=0.255 rows=1) (actual time=0.00211..0.00219 rows=1 loops=20)
The EXPLAIN ANALYZE statement only supports the TREE format. Each node represents an iterator. Each iterator outputs the estimated execution cost, the estimated number of returned rows, the time to return first row, time spent (in milliseconds) executing this iterator (including child iterators, but not parent iterators), and the number of rows returned by the iterator.
As of MySQL 8.0.32, if the explain_format system variable (which was introduced in MySQL 8.0.32) is set to TRADITIONAL, DEFAULT or TREE, then TREE is used. If the value is JSON, then an error is returned, unless FORMAT=TREE is specified as part of the statement.
I didn’t get an error in the above example because my explain_format system variable is set to TRADITIONAL. Therefore, it used TREE. But if my explain_format variable was set to JSON, then I would’ve received an error.
To demonstrate, let’s set explain_format to JSON and find out:
SET @@explain_format=JSON;And now run EXPLAIN ANALYZE again:
EXPLAIN ANALYZE
SELECT
`albums`.`AlbumName` AS `AlbumName`,
`artists`.`ArtistName` AS `ArtistName`,
`albums`.`ReleaseDate` AS `ReleaseDate`
FROM ((`albums`
JOIN `artists` ON((`albums`.`ArtistId` = `artists`.`ArtistId`)))
JOIN `genres` ON((`albums`.`GenreId` = `genres`.`GenreId`)));Result:
ERROR 1235 (42000): This version of MySQL doesn't yet support 'EXPLAIN ANALYZE with JSON format'
So it’s probably a good idea to explicitly include FORMAT=TREE in our EXPLAIN ANALYZE statement if we want to avoid such errors.
EXPLAIN for a Specific Connection
We can use EXPLAIN against a specific connection to get the execution plan for the statement executing in that connection.
To demonstrate this, let’s select the SLEEP() function and specify 20 seconds:
SELECT SLEEP(20);While that’s running, get a list of connections:
SHOW PROCESSLIST;Result:
+----+-----------------+-----------------+--------+---------+---------+------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-----------------+-----------------+--------+---------+---------+------------------------+------------------+ | 5 | event_scheduler | localhost | NULL | Daemon | 1150848 | Waiting on empty queue | NULL | | 94 | root | localhost | music | Query | 0 | init | SHOW PROCESSLIST | | 97 | root | localhost:52349 | sakila | Sleep | 513 | | NULL | | 98 | root | localhost:52353 | sakila | Query | 2 | User sleep | SELECT SLEEP(20) | +----+-----------------+-----------------+--------+---------+---------+------------------------+------------------+
We can see that connection 98 is the one using SELECT SLEEP(20).
While that’s still running, let’s now use EXPLAIN for that connection:
EXPLAIN FOR CONNECTION 98;Result:
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
It returned the information based on the connection ID that we passed.
Note that we can’t use FOR CONNECTION when using EXPLAIN ANALYZE.
EXPLAIN for a Specific Database
From MySQL 8.3, the EXPLAIN statement supports a FOR SCHEMA clause and its synonym FOR DATABASE. This allows us to run EXPLAIN as if it was executed in the specified database.
Here’s a simple example of using FOR SCHEMA:
USE PetHotel;
EXPLAIN FORMAT=TREE FOR SCHEMA sakila
SELECT * FROM actor;Result:
-> Table scan on actor (cost=21 rows=200)
In this example I changed to the PetHotel database (using USE PetHotel), then I ran EXPLAIN with FOR SCHEMA agains the sakila database.
Here’s what happens if I run it without FOR SCHEMA:
USE PetHotel;
EXPLAIN FORMAT=TREE
SELECT * FROM actor;Result:
ERROR 1146 (42S02): Table 'pethotel.actor' doesn't exist
The PetHotel database doesn’t actually have a table called actor, so we get an error.
If the specified database doesn’t exist we get an error:
USE PetHotel;
EXPLAIN FORMAT=TREE FOR SCHEMA oops
SELECT * FROM actor;Result:
ERROR 1049 (42000): Unknown database 'oops'
Also, we can’t use FOR SCHEMA and FOR CONNECTION in the same statement.
Privileges
EXPLAIN requires the same privileges required to execute the explained statement. It also requires the SHOW VIEW privilege for any explained view.
Also EXPLAIN … FOR CONNECTION requires the PROCESS privilege if the specified connection belongs to a different user.
More Information
See the MySQL documentation for more information about the EXPLAIN statement.