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        |                                   |                       |
+-----------------+-----------------------------------+-----------------------+
| DEPENDENT UNION | The UNION is DEPENDENT.           |                       |
+-----------------+-----------------------------------+-----------------------+
| DERIVED         | The SELECT is DERIVED from the    |                       |
|                 | PRIMARY.                          |                       |
+-----------------+-----------------------------------+-----------------------+
| MATERIALIZED    | The SUBQUERY is MATERIALIZED.     | Materialized tables   |
|                 |                                   | will be populated at  |
|                 |                                   | first access and      |
|                 |                                   | will be accessed by   |
|                 |                                   | the primary key (=    |
|                 |                                   | one key lookup).      |
|                 |                                   | Number of rows in     |
|                 |                                   | EXPLAIN shows the     |
|                 |                                   | cost of populating    |
|                 |                                   | the table             |
+-----------------+-----------------------------------+-----------------------+
| PRIMARY         | The SELECT is in the outermost    |                       |
|                 | query, but there is also a        |                       |
|                 | SUBQUERY within it.               |                       |
+-----------------+-----------------------------------+-----------------------+
| SIMPLE          | It is a simple SELECT query       |                       |
|                 | without any SUBQUERY or UNION.    |                       |
+-----------------+-----------------------------------+-----------------------+
| SUBQUERY        | The SELECT is a SUBQUERY of the   |                       |
|                 | PRIMARY.                          |                       |
+-----------------+-----------------------------------+-----------------------+
| UNCACHEABLE     | The SUBQUERY is UNCACHEABLE.      |                       |
| SUBQUERY        |                                   |                       |
+-----------------+-----------------------------------+-----------------------+
| UNCACHEABLE     | The UNION is UNCACHEABLE.         |                       |
| UNION           |                                   |                       |
+-----------------+-----------------------------------+-----------------------+
| UNION           | The SELECT is a UNION of the      |                       |
|                 | PRIMARY.                          |                       |
+-----------------+-----------------------------------+-----------------------+
| UNION RESULT    | The result of the UNION.          |                       |
+-----------------+-----------------------------------+-----------------------+
| LATERAL DERIVED | The SELECT uses a Lateral         |                       |
|                 | Derived optimization              |                       |
+-----------------+-----------------------------------+-----------------------+

"Type" Column
-------------

This column contains information on how the table is accessed.

+------------------------+---------------------------------------------------+
| Value                  | Description                                       |
+------------------------+---------------------------------------------------+
| ALL                    | A full table scan is done for the table (all      |
|                        | rows are read). This is bad if the table is       |
|                        | large and the table is joined against a previous  |
|                        | table!  This happens when the optimizer could     |
|                        | not find any usable index to access rows.         |
+------------------------+---------------------------------------------------+
| const                  | There is only one possibly matching row in the    |
|                        | table. The row is read before the optimization    |
|                        | phase and all columns in the table are treated    |
|                        | as constants.                                     |
+------------------------+---------------------------------------------------+
| eq_ref                 | A unique index is used to find the rows. This is  |
|                        | the best possible plan to find the row.           |
+------------------------+---------------------------------------------------+
| fulltext               | A fulltext index is used to access the rows.      |
+------------------------+---------------------------------------------------+
| index_merge            | A 'range' access is done for for several index    |
|                        | and the found rows are merged. The key column     |
|                        | shows which keys are used.                        |
+------------------------+---------------------------------------------------+
| index_subquery         | This is similar as ref, but used for sub queries  |
|                        | that are transformed to key lookups.              |
+------------------------+---------------------------------------------------+
| index                  | A full scan over the used index.  Better than     |
|                        | ALL but still bad if index is large and the       |
|                        | table is joined against a previous table.         |
+------------------------+---------------------------------------------------+
| range                  | The table will be accessed with a key over one    |
|                        | or more value ranges.                             |
+------------------------+---------------------------------------------------+
| ref_or_null            | Like 'ref' but in addition another search for     |
|                        | the 'null' value is done if the first value was   |
|                        | not found. This happens usually with sub queries. |
+------------------------+---------------------------------------------------+
| ref                    | A non unique index or prefix of an unique index   |
|                        | is used to find the rows. Good if the prefix      |
|                        | doesn't match many rows.                          |
+------------------------+---------------------------------------------------+
| system                 | The table has 0 or 1 rows.                        |
+------------------------+---------------------------------------------------+
| unique_subquery        | This is similar as eq_ref, but used for sub       |
|                        | queries that are transformed to key lookups       |
+------------------------+---------------------------------------------------+

"Extra" Column
--------------

This column consists of one or more of the following values, separated by ';'

Note that some of these values are detected after the optimization phase.

The optimization phase can do the following changes to the WHERE clause:

* Add the expressions from the ON and USING clauses to the WHERE
 clause.
* Constant propagation:  If there is column=constant, replace all column
 instances with this constant.
* Replace all columns from 'const' tables with their values.
* Remove the used key columns from the WHERE (as this will be tested as
 part of the key lookup).
* Remove impossible constant sub expressions.
 For example WHERE '(a=1 and a=2) OR b=1' becomes 'b=1'.
* Replace columns with other columns that has identical values:
 Example:  WHERE a=b and a=c may be treated
 as 'WHERE a=b and a=c and b=c'.
* Add extra conditions to detect impossible row conditions earlier. This
 happens mainly with OUTER JOIN where we in some cases add detection
 of NULL values in the WHERE (Part of 'Not exists' optimization).
 This can cause an unexpected 'Using where' in the Extra column.
* For each table level we remove expressions that have already been tested when
 we read the previous row. Example: When joining tables t1 with t2
 using the following WHERE 't1.a=1 and t1.a=t2.b', we don't have to
 test 't1.a=1' when checking rows in t2 as we already know that this
 expression is true.

+------------------------+---------------------------------------------------+
| Value                  | Description                                       |
+------------------------+---------------------------------------------------+
| const row not found    | The table was a system table (a table with        |
|                        | should exactly one row), but no row was found.    |
+------------------------+---------------------------------------------------+
| Distinct               | If distinct optimization (remove duplicates) was  |