Pager script for shrinking EXPLAIN output

Everyone who works with MySQL (or MariaDB) query optimizer has to spend a lot of time looking at EXPLAIN outputs. You typically first look at the tabular form, because it is easier to read. You can immediately see what the join order is, what numbers of records will be read, etc:

MariaDB [dbt3sf1]> explain select * from customer, orders where c_custkey= o_custkey;
+------+-------------+----------+------+---------------+-------------+---------+----------------------------+--------+-------+
| id   | select_type | table    | type | possible_keys | key         | key_len | ref                        | rows   | Extra |
+------+-------------+----------+------+---------------+-------------+---------+----------------------------+--------+-------+
|    1 | SIMPLE      | customer | ALL  | PRIMARY       | NULL        | NULL    | NULL                       | 150303 |       |
|    1 | SIMPLE      | orders   | ref  | i_o_custkey   | i_o_custkey | 5       | dbt3sf1.customer.c_custkey |      7 |       |
+------+-------------+----------+------+---------------+-------------+---------+----------------------------+--------+-------+

The only problem is that it quickly gets too wide and doesn’t fit even on wide screens. To relieve the pain, I wrote the script that shrinks EXPLAIN output by removing spaces and less useful information. You set the script as mysql client pager command:

MariaDB [dbt3sf1]> \P shrink_explain.pl
PAGER set to ’shrink_explain.pl’

and then the above EXPLAIN becomes shorter:

MariaDB [dbt3sf1]> explain select * from customer, orders where c_custkey= o_custkey;
+--+-----------+--------+----+-------------+-----------+-------+------------------+------+-----+
|id|select_type|table   |type|possible_keys|key        |key_len|ref               |rows  |Extra|
+--+-----------+--------+----+-------------+-----------+-------+------------------+------+-----+
|1 |SIMPLE     |customer|ALL |PRIMARY      |NULL       |NULL   |NULL              |150303|     |
|1 |SIMPLE     |orders  |ref |i_o_custkey  |i_o_custkey|5      |customer.c_custkey|7     |     |
+--+-----------+--------+----+-------------+-----------+-------+------------------+------+-----+

The worst horizontal space hog is the possible_keys column:

MariaDB [dbt3sf1]> explain select * from customer, orders where c_custkey= o_custkey and
    -> o_orderkey in (select l_orderkey from lineitem where l_shipdate='1995-01-01');
+------+--------------------+----------+----------------+-----------------------------------------------------------------------------------------------+-------------+---------+----------------------------+--------+-------------+
| id   | select_type        | table    | type           | possible_keys                                                                                 | key         | key_len | ref                        | rows   | Extra       |
+------+--------------------+----------+----------------+-----------------------------------------------------------------------------------------------+-------------+---------+----------------------------+--------+-------------+
|    1 | PRIMARY            | customer | ALL            | PRIMARY                                                                                       | NULL        | NULL    | NULL                       | 150303 |             |
|    1 | PRIMARY            | orders   | ref            | i_o_custkey                                                                                   | i_o_custkey | 5       | dbt3sf1.customer.c_custkey |      7 | Using where |
|    2 | DEPENDENT SUBQUERY | lineitem | index_subquery | PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity,l_shipdate_partkey,l_shipdate_suppkey | PRIMARY     | 4       | func                       |      2 | Using where |
+------+--------------------+----------+----------------+-----------------------------------------------------------------------------------------------+-------------+---------+----------------------------+--------+-------------+

I have added an option to aggressively shrink the possible_keys column:

MariaDB [dbt3sf1]> \P shrink_explain.pl –shrink-possible-keys
PAGER set to ’shrink_explain.pl –shrink-possible-keys’
MariaDB [dbt3sf1]> explain select * from customer, orders where c_custkey= o_custkey and
         ->  o_orderkey in (select l_orderkey from lineitem where l_shipdate=’1995-01-01′);
+--+---------------+--------+--------------+-------------+-----------+-------+------------------+------+-----------+
|id|select_type    |table   |type          |possible_keys|key        |key_len|ref               |rows  |Extra      |
+--+---------------+--------+--------------+-------------+-----------+-------+------------------+------+-----------+
|1 |PRIMARY        |customer|ALL           |PRIMARY      |NULL       |NULL   |NULL              |150303|           |
|1 |PRIMARY        |orders  |ref           |i_o_custkey  |i_o_custkey|5      |customer.c_custkey|7     |Using where|
|2 |DEPENDENT SUBQ.|lineitem|index_subquery|PRIMARY,i_...|PRIMARY    |4      |func              |2     |Using where|
+--+---------------+--------+--------------+-------------+-----------+-------+------------------+------+-----------+

and there is an option to remove possible_keys altogether:

MariaDB [dbt3sf1]> \P shrink_explain.pl –remove-possible-keys
PAGER set to ’shrink_explain.pl –remove-possible-keys’
MariaDB [dbt3sf1]> explain select * from customer, orders where c_custkey= o_custkey and
         -> o_orderkey in (select l_orderkey from lineitem where l_shipdate=’1995-01-01′);
+--+---------------+--------+--------------+---+-----------+-------+------------------+------+-----------+
|id|select_type    |table   |type          |...|key        |key_len|ref               |rows  |Extra      |
+--+---------------+--------+--------------+---+-----------+-------+------------------+------+-----------+
|1 |PRIMARY        |customer|ALL           |...|NULL       |NULL   |NULL              |150303|           |
|1 |PRIMARY        |orders  |ref           |...|i_o_custkey|5      |customer.c_custkey|7     |Using where|
|2 |DEPENDENT SUBQ.|lineitem|index_subquery|...|PRIMARY    |4      |func              |2     |Using where|
+--+---------------+--------+--------------+---+-----------+-------+------------------+------+-----------+

The script can be downloaded here: shrink_explain.pl.

Posted in Uncategorized, mysql on November 23rd, 2013 by spetrunia | |

Leave a reply

You must be logged in to post a comment.