SHOW EXPLAIN

This is a preliminary documentation for the SHOW EXPLAIN feature.

Command description

SHOW EXPLAIN command allows one to get an EXPLAIN (that is, a printout of a query plan) of a query that is 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:

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)

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

MariaDB [test]> 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:

MariaDB [test]> 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, query is written to slow/binary logs when its query plan is already deleted).

Required permissions

Same as in SHOW PROCESSLIST.

Partial query plans

MySQL/MariaDB optimizer will

• optimize parts of query lazily
• eagerly delete parts of query plan that are 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:

MariaDB [test]> 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:

MariaDB [test]> 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)

Where to get it

Currently, there is only bazaar repository, it is here: https://code.launchpad.net/~maria-captains/maria/5.5-show-explain

The repository is tracked in MariaDB buildbot: http://buildbot.askmonty.org/buildbot/grid?branch=5.5-show-explain

Differences between SHOW EXPLAIN and EXPLAIN outputs

Background

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. We have decided to keep the outputs different, because
  • EXPLAIN's behavior is not self-consistent and difficult to replicate.
  • There is little practical value in knowing the difference between PRIMARY and SIMPLE. For all practical purposes, they are the same.