{EXPLAIN | DESCRIBE | DESC}
tbl_name [col_name | wild]
{EXPLAIN | DESCRIBE | DESC}
[explain_type]
{explainable_stmt | FOR CONNECTION connection_id}
{EXPLAIN | DESCRIBE | DESC} ANALYZE [FORMAT = TREE] select_statement
explain_type: {
FORMAT = format_name
}
format_name: {
TRADITIONAL
| JSON
| TREE
}
explainable_stmt: {
SELECT statement
| TABLE statement
| DELETE statement
| INSERT statement
| REPLACE statement
| UPDATE statement
}
The DESCRIBE
and
EXPLAIN
statements are synonyms. In
practice, the DESCRIBE
keyword is
more often used to obtain information about table structure,
whereas EXPLAIN
is used to obtain a
query execution plan (that is, an explanation of how MySQL would
execute a query).
The following discussion uses the
DESCRIBE
and
EXPLAIN
keywords in accordance with
those uses, but the MySQL parser treats them as completely
synonymous.
DESCRIBE
provides information
about the columns in a table:
mysql> DESCRIBE City;
+------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+----------+------+-----+---------+----------------+
| Id | int(11) | NO | PRI | NULL | auto_increment |
| Name | char(35) | NO | | | |
| Country | char(3) | NO | UNI | | |
| District | char(20) | YES | MUL | | |
| Population | int(11) | NO | | 0 | |
+------------+----------+------+-----+---------+----------------+
DESCRIBE
is a shortcut for
SHOW COLUMNS
. These statements
also display information for views. The description for
SHOW COLUMNS
provides more
information about the output columns. See
Section 13.7.7.5, “SHOW COLUMNS Statement”.
By default, DESCRIBE
displays
information about all columns in the table.
col_name
, if given, is the name of a
column in the table. In this case, the statement displays
information only for the named column.
wild
, if given, is a pattern string.
It can contain the SQL %
and
_
wildcard characters. In this case, the
statement displays output only for the columns with names
matching the string. There is no need to enclose the string
within quotation marks unless it contains spaces or other
special characters.
The DESCRIBE
statement is
provided for compatibility with Oracle.
The SHOW CREATE TABLE
,
SHOW TABLE STATUS
, and
SHOW INDEX
statements also
provide information about tables. See Section 13.7.7, “SHOW Statements”.
The EXPLAIN
statement provides
information about how MySQL executes statements:
EXPLAIN
works withSELECT
,DELETE
,INSERT
,REPLACE
, andUPDATE
statements. In MySQL 8.0.19 and later, it also works withTABLE
statements.When
EXPLAIN
is used with an explainable statement, MySQL displays information from the optimizer about the statement execution plan. That is, MySQL explains how it would process the statement, including information about how tables are joined and in which order. For information about usingEXPLAIN
to obtain execution plan information, see Section 8.8.2, “EXPLAIN Output Format”.When
EXPLAIN
is used withFOR CONNECTION
rather than an explainable statement, it displays the execution plan for the statement executing in the named connection. See Section 8.8.4, “Obtaining Execution Plan Information for a Named Connection”.connection_id
For explainable statements,
EXPLAIN
produces additional execution plan information that can be displayed usingSHOW WARNINGS
. See Section 8.8.3, “Extended EXPLAIN Output Format”.EXPLAIN
is useful for examining queries involving partitioned tables. See Section 24.3.5, “Obtaining Information About Partitions”.The
FORMAT
option can be used to select the output format.TRADITIONAL
presents the output in tabular format. This is the default if noFORMAT
option is present.JSON
format displays the information in JSON format. In MySQL 8.0.16 and later,TREE
provides tree-like output with more precise descriptions of query handling than theTRADITIONAL
format; it is the only format which shows hash join usage (see Section 8.2.1.4, “Hash Join Optimization”) and is always used forEXPLAIN ANALYZE
.
EXPLAIN
requires the same
privileges required to execute the explained statement.
Additionally, EXPLAIN
also
requires the SHOW VIEW
privilege
for any explained view.
EXPLAIN ... FOR
CONNECTION
also requires the
PROCESS
privilege if the
specified connection belongs to a different user.
With the help of EXPLAIN
, you can
see where you should add indexes to tables so that the statement
executes faster by using indexes to find rows. You can also use
EXPLAIN
to check whether the
optimizer joins the tables in an optimal order. To give a hint
to the optimizer to use a join order corresponding to the order
in which the tables are named in a
SELECT
statement, begin the
statement with SELECT STRAIGHT_JOIN
rather
than just SELECT
. (See
Section 13.2.10, “SELECT Statement”.)
The optimizer trace may sometimes provide information
complementary to that of EXPLAIN
.
However, the optimizer trace format and content are subject to
change between versions. For details, see
MySQL
Internals: Tracing the Optimizer.
If you have a problem with indexes not being used when you
believe that they should be, run ANALYZE
TABLE
to update table statistics, such as cardinality
of keys, that can affect the choices the optimizer makes. See
Section 13.7.3.1, “ANALYZE TABLE Statement”.
MySQL Workbench has a Visual Explain capability that provides a
visual representation of
EXPLAIN
output. See
Tutorial: Using Explain to Improve Query Performance.
MySQL 8.0.18 introduces EXPLAIN ANALYZE
,
which runs a statement and produces
EXPLAIN
output along with timing
and additional, iterator-based, information about how the
optimizer's expectations matched the actual execution. For
each iterator, the following information is provided:
Estimated execution cost
(Some iterators are not accounted for by the cost model, and so are not included in the estimate.)
Estimated number of returned rows
Time to return first row
Time to return all rows (actual cost), in milliseconds
(When there are multiple loops, this figure shows the average time per loop.)
Number of rows returned by the iterator
Number of loops
The query execution information is displayed using the
TREE
output format, in which nodes represent
iterators. EXPLAIN ANALYZE
always uses the
TREE
output format. In MySQL 8.0.21 and
later, this can optionally be specified explicitly using
FORMAT=TREE
; formats other than
TREE
remain unsupported.
EXPLAIN ANALYZE
can be used with
SELECT
statements, as well as
with multi-table UPDATE
and
DELETE
statements. Beginning with
MySQL 8.0.19, it can also be used with
TABLE
statements.
Beginning with MySQL 8.0.20, you can terminate this statement
using KILL QUERY
or CTRL-C.
EXPLAIN ANALYZE
cannot be used with
FOR CONNECTION
.
Example output:
mysql> EXPLAIN ANALYZE SELECT * FROM t1 JOIN t2 ON (t1.c1 = t2.c2)\G
*************************** 1. row ***************************
EXPLAIN: -> Inner hash join (t2.c2 = t1.c1) (cost=4.70 rows=6)
(actual time=0.032..0.035 rows=6 loops=1)
-> Table scan on t2 (cost=0.06 rows=6)
(actual time=0.003..0.005 rows=6 loops=1)
-> Hash
-> Table scan on t1 (cost=0.85 rows=6)
(actual time=0.018..0.022 rows=6 loops=1)
mysql> EXPLAIN ANALYZE SELECT * FROM t3 WHERE i > 8\G
*************************** 1. row ***************************
EXPLAIN: -> Filter: (t3.i > 8) (cost=1.75 rows=5)
(actual time=0.019..0.021 rows=6 loops=1)
-> Table scan on t3 (cost=1.75 rows=15)
(actual time=0.017..0.019 rows=15 loops=1)
mysql> EXPLAIN ANALYZE SELECT * FROM t3 WHERE pk > 17\G
*************************** 1. row ***************************
EXPLAIN: -> Filter: (t3.pk > 17) (cost=1.26 rows=5)
(actual time=0.013..0.016 rows=5 loops=1)
-> Index range scan on t3 using PRIMARY (cost=1.26 rows=5)
(actual time=0.012..0.014 rows=5 loops=1)
The tables used in the example output were created by the statements shown here:
CREATE TABLE t1 (
c1 INTEGER DEFAULT NULL,
c2 INTEGER DEFAULT NULL
);
CREATE TABLE t2 (
c1 INTEGER DEFAULT NULL,
c2 INTEGER DEFAULT NULL
);
CREATE TABLE t3 (
pk INTEGER NOT NULL PRIMARY KEY,
i INTEGER DEFAULT NULL
);
Values shown for actual time
in the output of
this statement are expressed in milliseconds.