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

{EXPLAIN | DESCRIBE | DESC}
    [explain_type]
    explainable_stmt

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 As of MySQL 5.6.3, permitted explainable statements for EXPLAIN are
  SELECT, DELETE, INSERT, REPLACE, and UPDATE. Before MySQL 5.6.3,
  SELECT is the only explainable statement.

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.6/en/explain-output.html.

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

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

o As of MySQL 5.6.5, 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.6/en/select.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.6/en/explain.html

