Syntax:
{EXPLAIN | DESCRIBE | DESC}
    tbl_name [col_name | wild]

{EXPLAIN | DESCRIBE | DESC}
    [explain_type]
    {explainable_stmt | FOR CONNECTION connection_id}

explain_type: {
    EXTENDED
  | PARTITIONS
  | FORMAT = format_name
}

format_name: {
    TRADITIONAL
  | JSON
}

explainable_stmt: {
    SELECT 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.

Obtaining Table Structure Information

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 [HELP SHOW COLUMNS].

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 [HELP SHOW].

Obtaining Execution Plan Information

The EXPLAIN statement provides information about how MySQL executes
statements:

o In MySQL 5.7, permitted explainable statements for EXPLAIN are
  SELECT, DELETE, INSERT, REPLACE, and UPDATE.

o 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 using EXPLAIN to obtain execution plan information,
  see http://dev.mysql.com/doc/refman/5.7/en/explain-output.html.

o When EXPLAIN is used with FOR CONNECTION connection_id rather than an
  explainable statement, it displays the execution plan for the
  statement executing in the named connection. See
  http://dev.mysql.com/doc/refman/5.7/en/explain-for-connection.html.

o EXPLAIN EXTENDED can be used to obtain additional execution plan
  information. See
  http://dev.mysql.com/doc/refman/5.7/en/explain-extended.html.

  As of MySQL 5.7.3, the EXPLAIN statement is changed so that the
  effect of the EXTENDED keyword is always enabled. EXTENDED is still
  recognized for backward compatibility, but is superfluous and is
  deprecated; its use results in a warning. It will be removed from
  EXPLAIN syntax in a future MySQL release.

o EXPLAIN PARTITIONS is useful for examining queries involving
  partitioned tables. See
  http://dev.mysql.com/doc/refman/5.7/en/partitioning-info.html.

  As of MySQL 5.7.3, the EXPLAIN statement is changed so that the
  effect of the PARTITIONS keyword is always enabled. PARTITIONS is
  still recognized for backward compatibility, but is superfluous and
  is deprecated; its use results in a warning. It will be removed from
  EXPLAIN syntax in a future MySQL release.

o The FORMAT option can be used to select the output format.
  TRADITIONAL presents the output in tabular format. This is the
  default if no FORMAT option is present. JSON format displays the
  information in JSON format. With FORMAT = JSON, the output includes
  extended and partition information.

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
http://dev.mysql.com/doc/refman/5.7/en/select.html.)

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
(http://dev.mysql.com/doc/internals/en/optimizer-tracing.html).

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 [HELP ANALYZE TABLE].

URL: http://dev.mysql.com/doc/refman/5.7/en/explain.html

