Syntax
------

SHOW EXPLAIN [FORMAT=JSON] FOR <connection_id>;
EXPLAIN [FORMAT=JSON] FOR CONNECTION <connection_id>;

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

The SHOW EXPLAIN command allows one to get an EXPLAIN (that is, a description
of a query plan) of a query running in a certain connection.

SHOW EXPLAIN FOR <connection_id>;

will produce an EXPLAIN output for the query that connection number
connection_id is running. The connection id can be obtained with SHOW
PROCESSLIST.

SHOW EXPLAIN FOR 1;
+------+-------------+-------+-------+---------------+------+---------+------+-
-------+-------------+
| id   | select_type | table | type  | possible_keys | key  | key_len | ref  |
rows    | Extra       |
+------+-------------+-------+-------+---------------+------+---------+------+-
-------+-------------+
|    1 | SIMPLE      | tbl   | index | NULL          | a    | 5       | NULL |
1000107 | Using index |
+------+-------------+-------+-------+---------------+------+---------+------+-
-------+-------------+
1 row in set, 1 warning (0.00 sec)

The output is always accompanied with a warning which shows the query the
target connection is running (this shows what the EXPLAIN is for):

SHOW WARNINGS;
+-------+------+------------------------+
| Level | Code | Message                |
+-------+------+------------------------+
| Note  | 1003 | select sum(a) from tbl |
+-------+------+------------------------+
1 row in set (0.00 sec)

EXPLAIN FOR CONNECTION
----------------------

MariaDB starting with 10.9
--------------------------
The EXPLAIN FOR CONNECTION syntax was added for MySQL compatibility.

FORMAT=JSON
-----------

MariaDB starting with 10.9
--------------------------
SHOW EXPLAIN [FORMAT=JSON] FOR <connection_id> extends SHOW EXPLAIN to return
more detailed JSON output.

Possible Errors
---------------

The output can be only produced if the target connection is currently running
a query, which has a ready query plan. If this is not the case, the output
will be:

SHOW EXPLAIN FOR 2;
ERROR 1932 (HY000): Target is not running an EXPLAINable command

You will get this error when:

* the target connection is not running a command for which one can run EXPLAIN
* the target connection is running a command for which one can run EXPLAIN, but
there is no query plan yet (for example, tables are open and locks are
 acquired before the query plan is produced)

Differences Between SHOW EXPLAIN and EXPLAIN Outputs
----------------------------------------------------

Background
----------

In MySQL, EXPLAIN execution takes a slightly different route from the way the
real query (typically the SELECT) is optimized. This is unfortunate, and has
caused a number of bugs in EXPLAIN. (For example, see MDEV-326, MDEV-410, and
lp:1013343. lp:992942 is not directly about EXPLAIN, but it also would not
have existed if MySQL didn't try to delete parts of a query plan in the middle
of the query)

SHOW EXPLAIN examines a running SELECT, and hence its output may be slightly
different from what EXPLAIN SELECT would produce. We did our best to make sure
that either the difference is negligible, or SHOW EXPLAIN's output is closer
to reality than EXPLAIN's output.

List of Recorded Differences
----------------------------

* SHOW EXPLAIN may have Extra='no matching row in const table', where  EXPLAIN
would produce Extra='Impossible WHERE ...'
* For queries with subqueries, SHOW EXPLAIN may print select_type==PRIMARY
where regular EXPLAIN used to print select_type==SIMPLE, or vice versa.

Required Permissions
--------------------

Running SHOW EXPLAIN requires the same permissions as running SHOW PROCESSLIST
would.

URL: https://mariadb.com/kb/en/show-explain/