SHOW EXPLAIN
MariaDB starting with 10.0.0
The SHOW EXPLAIN
command is a new feature in MariaDB 10.0.0.
Contents
Command description
The SHOW EXPLAIN
command allows one to get an EXPLAIN
(that is, a
printout 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:
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)
Possible errors
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:
- the target thread is not running a command for which one can run
EXPLAIN
- the target thread 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)
- there is no query plan already (for example, the query is written to slow/binary logs when its query plan is already deleted).
Partial query plans
The MySQL/MariaDB optimizer will:
- optimize parts of query lazily
- eagerly delete parts of query plan which are no longer needed
SHOW EXPLAIN
will print whatever parts of query plan are currently
available. If a part of the plan is not available, you may see
"Not yet optimized"
or "Query plan already deleted"
in the Extra
column.
For example, for this query:
EXPLAIN SELECT column1 + 1 FROM temp_table_view; +------+-------------+------------+------+---------------+------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+------------+------+---------------+------+---------+------+------+-------+ | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 2 | | | 2 | DERIVED | t1 | ALL | NULL | NULL | NULL | NULL | 2 | | +------+-------------+------------+------+---------------+------+---------+------+------+-------+ 2 rows in set (0.00 sec)
one may get this output:
show explain for 1; +------+-------------+------------+------+---------------+------+---------+------+------+----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+------------+------+---------------+------+---------+------+------+----------------------------+ | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 2 | | | 2 | DERIVED | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Query plan already deleted | +------+-------------+------------+------+---------------+------+---------+------+------+----------------------------+ 2 rows in set, 1 warning (0.01 sec)
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
', whereEXPLAIN
would produce Extra='Impossible WHERE ...
'- For queries with subqueries,
SHOW EXPLAIN
may printselect_type==PRIMARY
where regularEXPLAIN
used to printselect_type==SIMPLE
, or vice versa.
Required permissions
Running SHOW EXPLAIN
requires the same permissions as
running SHOW PROCESSLIST
would.
See also
- EXPLAIN ANALYZE, which performs a query and outputs enhanced EXPLAIN results.