Is there a way to show MYsql explain plan like Oracle as a tree

Question

Sample query:

create table t1(c1 int primary key);

MySQL

mysql> explain select c1 from t1;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | index | NULL          | PRIMARY | 4       | NULL |    1 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

Oracle:

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2822583898

------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |     1 |    13 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T_DILANG |     1 |    13 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------

Note
-----

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   - dynamic sampling used for this statement (level=2)

12 rows selected.

A lot of information can be got from the tree like explain plan. It will be great if MySQL can show that too.

Update:

Using Percona Toolkit, pt-visual-explain, it works well.

Command line output:

mysql> explain select c1 from t1, A, B where c1 > 0 group by c1 having count(1) > 2 limit 1;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                                                     |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------------------------------------------+
|  1 | SIMPLE      | t1    | NULL       | index | PRIMARY       | PRIMARY | 4       | NULL |    1 |   100.00 | Using where; Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | A     | NULL       | index | NULL          | idx7    | 4       | NULL |    1 |   100.00 | Using index; Using join buffer (Block Nested Loop)        |
|  1 | SIMPLE      | B     | NULL       | index | NULL          | idx7    | 4       | NULL |    1 |   100.00 | Using index; Using join buffer (Block Nested Loop)        |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------------------------------------------+
3 rows in set, 1 warning (0.00 sec)

Output from pt-visual-explain:

$mysql -e "explain select c1 from t1, A, B where c1 > 0 group by c1 having count(1) > 2 limit 1" | pt-visual-explain
Filesort
+- TEMPORARY
   table          temporary(t1,A,B)
   +- JOIN
      +- Join buffer
      |  +- Index scan
      |     key            B->idx7
      |     key_len        4
      |     rows           1
      +- JOIN
         +- Join buffer
         |  +- Index scan
         |     key            A->idx7
         |     key_len        4
         |     rows           1
         +- Filter with WHERE
            +- Index scan
               key            t1->PRIMARY
               possible_keys  PRIMARY
               key_len        4
               rows           1

Answer 1

The new MySQL workbench comes with Visual Explain Plan which shows a flow chart to explain the query. Apart from that in the commandline or mysql, there is not much.

https://dev.mysql.com/doc/workbench/en/wb-performance-explain.html

If you still like command line you can use Percona's Visual Explain tool however it'll require explain plan as given input.

mysql -e "explain select c1 from t1" | pt-visual-explain

https://www.percona.com/doc/percona-toolkit/2.2/pt-visual-explain.html