Syntax\n------\n\nEXPLAIN tbl_name [col_name | wildcard]\n\nDescription\n-----------\n\nThe 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:\n\n 'EXPLAIN tbl_name' is synonymous with DESCRIBE or SHOW COLUMNS ... FROM _mytable_.\n 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.\n EXPLAIN PARTITIONS is useful only when examining queries involving partitioned tables. For details, see Partition pruning and selection.\n The ANALYZE statement is similar to EXPLAIN, but it runs the query rather than only estimating the execution path. Hence, the ANALYZE statement provides both estimated and real execution statistics. This includes the r_rows and r_filtered columns, which reflect the actual number of rows evaluated and filtered during query execution.\n EXPLAIN output can be printed in the slow query log. See EXPLAIN in the Slow Query Log for details.\n EXPLAIN FOR CONNECTION is an alias for SHOW EXPLAIN FOR.\n\nSHOW EXPLAIN shows the output of a running statement. In some cases, its output can be closer to reality than EXPLAIN.\n\nThe 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.\n\nThere is an online EXPLAIN Analyzer that you can use to share EXPLAIN and EXPLAIN EXTENDED output with others.\n\nEXPLAIN can acquire metadata locks in the same way that SELECT does, as it needs to know table metadata and, sometimes, data as well.\n\nColumns in EXPLAIN ... SELECT\n\n| Column name    | Description                                                                                         |\n| -------------- | --------------------------------------------------------------------------------------------------- |\n| id             | Sequence number that shows in which order tables are joined.                                        |\n| select_type   | What kind of SELECT the table comes from.                                                         |\n| table          | Alias name of table. Materialized temporary tables for sub queries are named <subquery#>          |\n| type           | How rows are found from the table (join type).                                                      |\n| possible_keys | keys in table that could be used to find rows in the table                                          |\n| key            | The name of the key that is used to retrieve rows. NULL means no key was used.                    |\n| key_len       | How many bytes of the key that was used (shows if we are using only parts of the multi-column key). |\n| ref            | The reference that is used as the key value.                                                        |\n| rows           | An estimate of how many rows we will find in the table for each key lookup.                         |\n| Extra          | Extra information about this join.                                                                  |\n\nHere are descriptions of the values for some of the more complex columns in EXPLAIN ... SELECT:\n\nSelect_type Column\n\nThe select_type column can have the following values:\n\n| Value                | Description                                                                                                                                                                                     | Comment                                                                                                                                                                              |\n| -------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |\n| DEPENDENT SUBQUERY   | The SUBQUERY is DEPENDENT.                                                                                                                                                                  |                                                                                                                                                                                      |\n| DEPENDENT UNION      | The UNION is DEPENDENT.                                                                                                                                                                     |                                                                                                                                                                                      |\n| DERIVED              | The SELECT is DERIVED from the PRIMARY.                                                                                                                                                   |                                                                                                                                                                                      |\n| 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 |\n| PRIMARY              | The SELECT is in the outermost query, but there is also a SUBQUERY within it.                                                                                                               |                                                                                                                                                                                      |\n| SIMPLE               | It is a simple SELECT query without any SUBQUERY or UNION.                                                                                                                                |                                                                                                                                                                                      |\n| SUBQUERY             | The SELECT is a SUBQUERY of the PRIMARY.                                                                                                                                                  |                                                                                                                                                                                      |\n| UNCACHEABLE SUBQUERY | The SUBQUERY is UNCACHEABLE.                                                                                                                                                                |                                                                                                                                                                                      |\n| UNCACHEABLE UNION    | The UNION is UNCACHEABLE.                                                                                                                                                                   |                                                                                                                                                                                      |\n| UNION                | The SELECT is a UNION of the PRIMARY.                                                                                                                                                     |                                                                                                                                                                                      |\n| UNION RESULT         | The result of the UNION.                                                                                                                                                                      |                                                                                                                                                                                      |\n| LATERAL DERIVED      | The SELECT uses a Lateral Derived optimization |                                                                                                                                                                                      |\n\nType Column\n\nThis column contains information on how the table is accessed.\n\n| Value            | Description                                                                                                                                                                                                                      |\n| ---------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |\n| 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. |\n| 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.                                                                       |\n| eq_ref          | A unique index is used to find the rows. This is the best possible plan to find the row.                                                                                                                                         |\n| filter           | A second index is being used with the Rowid Filtering Optimization.                                                |\n| fulltext         | A fulltext index is used to access the rows.                                                                                                                                                                                     |\n| index_merge     | A 'range' access is done for several index and the found rows are merged. The key column shows which keys are used.                                                                                                              |\n| index_subquery  | This is similar as ref, but used for sub queries that are transformed to key lookups.                                                                                                                                            |\n| 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.                                                                                             |\n| range            | The table will be accessed with a key over one or more value ranges.                                                                                                                                                             |\n| 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.                                                                                  |\n| 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.                                                                                                            |\n| system           | The table has 0 or 1 rows.                                                                                                                                                                                                       |\n| unique_subquery | This is similar as eq_ref, but used for sub queries that are transformed to key lookups                                                                                                                                         |\n\nExtra Column\n\nThis column consists of one or more of the following values, separated by ';'\n\n_Note that some of these values are detected after the optimization phase._\n\nThe optimization phase can do the following changes to the WHERE clause:\n\n Add the expressions from the ON and USING clauses to the WHERE clause.\n Constant propagation: If there is column=constant, replace all column instances with this constant.\n Replace all columns from 'const' tables with their values.\n Remove the used key columns from the WHERE (as this will be tested as part of the key lookup).\n Remove impossible constant sub expressions. For example WHERE '(a=1 and a=2) OR b=1' becomes 'b=1'.\n 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'.\n 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.\n 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.\n\n| Value                                                | Description                                                                                                                                                                                                                                                                                                                                                                                                                                                          |\n| ---------------------------------------------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |\n| const row not found\n\n[Truncated — full content at mariadb.com/docs]