SHOW EXPLAIN

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 with no matching row in const table in the EXTRA column, WHERE EXPLAIN would have produced Impossible WHERE ...:

  1      SIMPLE  NULL    NULL    NULL    NULL    NULL    NULL    NULL    no matching row in const table

• For queries with subqueries, SHOW EXPLAIN may print select_type==PRIMARY where regular EXPLAIN used to print select_type==SIMPLE, or vice versa.