The SHOW EXPLAIN
command is a new feature in MariaDB 10.0.0.
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 thread.
The syntax is:
SHOW EXPLAIN FOR <thread_id>;
which will produce an EXPLAIN
output for the query that thread number thread_id
is running. The thread 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 thread 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)
The output can be only produced if the target thread 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:
EXPLAIN
EXPLAIN
, but 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.
SHOW EXPLAIN
may have Extra='no matching row in const table
', where EXPLAIN
would produce Extra='Impossible WHERE ...
' SHOW EXPLAIN
may print select_type==PRIMARY
where regular EXPLAIN
used to print select_type==SIMPLE
, or vice versa. Running SHOW EXPLAIN
requires the same permissions as running SHOW PROCESSLIST
would.
EXPLAIN ANALYZE
, which will perform a query and outputs enhanced EXPLAIN
results.
© 2019 MariaDB
Licensed under the Creative Commons Attribution 3.0 Unported License and the GNU Free Documentation License.
https://mariadb.com/kb/en/show-explain/