MariaDB 10.0 has SHOW EXPLAIN feature. It was coded by yours truly and first introduced about a year ago. Last release of MySQL 5.7 introduced EXPLAIN FOR CONNECTION, which looks rather similar to MariaDB’s SHOW EXPLAIN. I was wondering how these two compare.
The basics
The usage scenarios are similar. In both cases, you first need a connection id of a running query. It is typically obtained by running SHOW PROCESSLIST:
MariaDB [(none)]> show processlist;+----+------+-----------+----------+---------+------+--------------+-----------------------------+----------+| Id | User | Host | db | Command | Time | State | Info | Progress |+----+------+-----------+----------+---------+------+--------------+-----------------------------+----------+| 2 | root | localhost | dbt3sf10 | Query | 2 | Sending data | select count(*) from orders | 0.000 | | 3 | root | localhost | NULL | Query | 0 | init | show processlist | 0.000 |+----+------+-----------+----------+---------+------+--------------+-----------------------------+----------+
Then, in MariaDB, you run:
MariaDB [(none)]> show explain for 2;+------+-------------+--------+-------+---------------+---------------+---------+------+----------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+------+-------------+--------+-------+---------------+---------------+---------+------+----------+-------------+| 1 | SIMPLE | orders | index | NULL | i_o_orderdate | 4 | NULL | 14856637 | Using index |+------+-------------+--------+-------+---------------+---------------+---------+------+----------+-------------+
and you get the EXPLAIN for the query that connection #2 is running. SHOW EXPLAIN always generates a warning, the warning has the text of the query that the EXPLAIN is for:
MariaDB [(none)]> show warnings;+-------+------+-----------------------------+| Level | Code | Message |+-------+------+-----------------------------+| Note | 1003 | select count(*) from orders |+-------+------+-----------------------------+
The idea behind this was that without the warning, it will be difficult to tell which query this EXPLAIN belongs to. It could be that the query you saw in SHOW PROCESSLIST has finished and another one has started.
SHOW EXPLAIN works for any query that EXPLAIN works for (starting from MariaDB 10.0.5 EXPLAIN UPDATE/DELETE/INSERT are supported). If you attempt to run SHOW EXPLAIN on a connection that is not running a statement that has EXPLAIN, you will get:
MariaDB [(none)]> show explain for 2; ERROR 1933 (HY000): Target is not running an EXPLAINable command
Now, let’s take a look at MySQL’s EXPLAIN FOR CONNECTION:
MySQL [(none)]> explain for connection 1;+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+| 1 | SIMPLE | orders | ALL | NULL | NULL | NULL | NULL | 922880 | Using where |+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+
Looks very similar to SHOW EXPLAIN, but there is no warning with query text. The only way out I can see is to run SHOW PROCESSLIST again, find your query there, and look at the “Time” column. If the value is big enough, this means that the query you see in SHOW PROCESSLIST output was already running when you ran SHOW EXPLAIN.
If the target connection is not running a query, you will get nothing
MySQL [(none)]> explain for connection 1; Query OK, 0 rows affected (0.00 sec)
More details
Unlike SHOW EXPLAIN, EXPLAIN FOR CONNECTION supports flags. That is, you can run EXPLAIN [EXTENDED|PARTITIONS|FORMAT=JSON] FOR CONNECTION . However, EXPLAIN EXTENDED will not print the warning with query after rewrites, and EXPLAIN FORMAT=JSON will not print attached_condition entries. I think, these are caused by limitations of EXPLAIN FOR CONNECTION code.
There are cases where EXPLAIN FOR CONNECTION will produce outputs like:
MySQL [(none)]> explain for connection 1;+----+-------------+-------+------+---------------+------+---------+------+------+----------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+------+---------------+------+---------+------+------+----------------------+| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Plan isn't ready yet |+----+-------------+-------+------+---------------+------+---------+------+------+----------------------+
In some cases, a part of the plan is not ready:
MySQL [(none)]> explain for connection 1;+----+-------------+----------+------+---------------+------+---------+------+--------+----------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+----------+------+---------------+------+---------+------+--------+----------------------+| 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Plan isn't ready yet | | 2 | SUBQUERY | lineitem | ALL | NULL | NULL | NULL | NULL | 974084 | NULL |+----+-------------+----------+------+---------------+------+---------+------+--------+----------------------+
SHOW EXPLAIN in MariaDB could produce similar partial query plans, but after release 10.0.5, there is always a full query plan. It would be interesting to discuss the reasons for this, but the discussion won’t fit into this blog post.
Another interesting question is whether MySQL 5.7’s EXPLAIN FOR CONNECTION allows for printing of query plan into slow query log. Before MariaDB 10.0.5, SHOW EXPLAIN code didn’t allow this. Changes in MariaDB 10.0.5 made printing EXPLAIN at the query end easy, and now MariaDB can save EXPLAINs in the Slow Query Log. Will MySQL 5.7 follow and also add such feature?
Sergei Petrunia's blog 