Syntax
------

EXPLAIN tbl_name [col_name | wild]

Or

EXPLAIN [EXTENDED | PARTITIONS | FORMAT=JSON] 
 {SELECT select_options | UPDATE update_options | DELETE delete_options}

Description
-----------

The EXPLAIN statement can be used either as a synonym for DESCRIBE or as a way
to obtain information about how MariaDB executes a SELECT, UPDATE or DELETE
statement:

* 'EXPLAIN tbl_name' is synonymous with 
 'DESCRIBE tbl_name' or
 'SHOW COLUMNS FROM tbl_name'.
* When you precede a SELECT, UPDATE or a DELETE statement with the keyword 
 EXPLAIN, MariaDB displays information from the optimizer
 about the query execution plan. That is, MariaDB explains how it would
 process the SELECT, UPDATE or DELETE, including information about how tables
 are joined and in which order. EXPLAIN EXTENDED can be
 used to provide additional information.
* EXPLAIN PARTITIONS is useful only when examining queries involving
partitioned tables. For details, see Partition pruning and selection.
* ANALYZE statement performs the query as well as producing EXPLAIN output,
and provides actual as well as estimated statistics.
* EXPLAIN output can be printed in the slow query log. See EXPLAIN in the Slow
Query Log for details.

SHOW EXPLAIN shows the output of a running statement. In some cases, its
output can be closer to reality than EXPLAIN.

The ANALYZE statement runs a statement and returns information about its
execution plan. It also shows additional columns, to check how much the
optimizer's estimation about filtering and found rows are close to reality.

There is an online EXPLAIN Analyzer that you can use to share EXPLAIN and
EXPLAIN EXTENDED output with others.

EXPLAIN can acquire metadata locks in the same way that SELECT does, as it
needs to know table metadata and, sometimes, data as well.

Columns in EXPLAIN ... SELECT
-----------------------------

+--------------------------------------+--------------------------------------+
| Column name                          | Description                          |
+--------------------------------------+--------------------------------------+
| id                                   | Sequence number that shows in which  |
|                                      | order tables are joined.             |
+--------------------------------------+--------------------------------------+
| select_type                          | What kind of SELECT the table comes  |
|                                      | from.                                |
+--------------------------------------+--------------------------------------+
| table                                | Alias name of table. Materialized    |
|                                      | temporary tables for sub queries     |
|                                      | are named <subquery#>                |
+--------------------------------------+--------------------------------------+
| type                                 | How rows are found from the table    |
|                                      | (join type).                         |
+--------------------------------------+--------------------------------------+
| possible_keys                        | keys in table that could be used to  |
|                                      | find rows in the table               |
+--------------------------------------+--------------------------------------+
| key                                  | The name of the key that is used to  |
|                                      | retrieve rows. NULL is no key was    |
|                                      | used.                                |
+--------------------------------------+--------------------------------------+
| key_len                              | How many bytes of the key that was   |
|                                      | used (shows if we are using only     |
|                                      | parts of the multi-column key).      |
+--------------------------------------+--------------------------------------+
| ref                                  | The reference that is used as the    |
|                                      | key value.                           |
+--------------------------------------+--------------------------------------+
| rows                                 | An estimate of how many rows we      |
|                                      | will find in the table for each key  |
|                                      | lookup.                              |
+--------------------------------------+--------------------------------------+
| Extra                                | Extra information about this join.   |
+--------------------------------------+--------------------------------------+

Here are descriptions of the values for some of the more complex columns in
EXPLAIN ... SELECT:

"Select_type" Column
--------------------

The select_type column can have the following values:

+-----------------+-----------------------------------+-----------------------+
| Value           | Description                       | Comment               |
+-----------------+-----------------------------------+-----------------------+
| DEPENDENT       | The SUBQUERY is DEPENDENT.        |                       |
| SUBQUERY        |                                   |                       |
+-----------------+-----------------------------------+-----------------------+