SHOW EXPLAIN
You are viewing an old version of this article. View
the current version here.
This is a preliminary documentation for SHOW EXPLAIN feature.
Syntax
It looks like this:
MariaDB [test]> 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) MariaDB [test]> show warnings; +-------+------+------------------------+ | Level | Code | Message | +-------+------+------------------------+ | Note | 1003 | select sum(a) from tbl | +-------+------+------------------------+ 1 row in set (0.00 sec)
The code
The latest code is here:
https://code.launchpad.net/~maria-captains/maria/5.5-show-explain
Differences from EXPLAIN output
In MySQL, there are parts of code where EXPLAIN
execution takes different route from actual query execution. When one runs SHOW EXPLAIN
on a running SELECT
query, it will examine different data structures, and hence the output will be slightly different.
One could argue that output of SHOW EXPLAIN
is the correct one. There will be a motion to switch EXPLAIN
to produce the same output like SHOW EXPLAIN
does.
The list of differences follows:
SHOW EXPLAIN
may produce EXPLAIN lines withno matching row in const table
in theEXTRA
column, WHEREEXPLAIN
would have producedImpossible WHERE ...
:
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL no matching row in const table
- For queries with subqueries,
SHOW EXPLAIN
may printselect_type==PRIMARY
where regularEXPLAIN
used to printselect_type==SIMPLE
, or vice versa. We have decided to keep the outputs different, becauseEXPLAIN
's behavior is not self-consistent and difficult to replicate.- There is little practical value in knowing the difference between
PRIMARY
andSIMPLE
. For all practical purposes, they are the same.
Comments
Comments loading...
Content reproduced on this site is the property of its respective owners,
and this content is not reviewed in advance by MariaDB. The views, information and opinions
expressed by this content do not necessarily represent those of MariaDB or any other party.