This is the first in a series of posts highlighting a few of the seldom-used but still handy Percona Toolkit tools.
Have you ever had a problem understanding the EXPLAIN statement output? And are you the type of person who would rather use the command line than a GUI application? Then I would recommend that you use Percona’s pt-visual-explain toolkit. This is one of many Percona Toolkit tools that is useful for those who want to have a different view and an easier time understanding the EXPLAIN output aside from the usual table and vertical views.
As described in the documentation – https://www.percona.com/doc/percona-toolkit/2.2/pt-visual-explain.html#description
“pt-visual-explain reverse-engineers MySQL’s EXPLAIN output into a query execution plan, which it then formats as a left-deep tree – the same way the plan is represented inside MySQL.”
Our sample query:
SELECT City.Name, Country.Name FROM City JOIN Country on (City.CountryCode=Country.Code)
In Table view the EXPLAIN output would look like:
1 2 3 4 5 6 7 | $ mysql -Dworld -e "explain SELECT City.Name, Country.Name FROM City JOIN Country on (City.CountryCode=Country.Code)" +----+-------------+---------+------+---------------+-------------+---------+--------------------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+------+---------------+-------------+---------+--------------------+------+-------+ | 1 | SIMPLE | Country | ALL | PRIMARY | NULL | NULL | NULL | 183 | | | 1 | SIMPLE | City | ref | CountryCode | CountryCode | 3 | world.Country.Code | 1 | | +----+-------------+---------+------+---------------+-------------+---------+--------------------+------+-------+ |
While on vertical view, the EXPLAIN output would be:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | $ mysql -Dworld -e "explain SELECT City.Name, Country.Name FROM City JOIN Country on (City.CountryCode=Country.Code)G" *************************** 1. row *************************** id: 1 select_type: SIMPLE table: Country type: ALL possible_keys: PRIMARY key: NULL key_len: NULL ref: NULL rows: 183 Extra: *************************** 2. row *************************** id: 1 select_type: SIMPLE table: City type: ref possible_keys: CountryCode key: CountryCode key_len: 3 ref: world.Country.Code rows: 1 Extra: |
Using the tool…
The EXPLAIN output in pt-visual-explain tree format, I will be showing you three different ways on how to use this handy toolkit.
Piping input into pt-visual-explain, like the one shown below:
1 | $ mysql -Dworld -e "explain SELECT City.Name, Country.Name FROM City JOIN Country on (City.CountryCode=Country.Code)" | pt-visual-explain |
Parsing input to a file and then pt-visual-explain will reverse-engineer the EXPLAIN output to a query execution plan and formats it to a tree view, as shown below:
1 2 3 4 5 6 | $ mysql -Dworld -e "explain SELECT City.Name, Country.Name FROM City JOIN Country on (City.CountryCode=Country.Code)" > explain.out $ cat explain.out id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE Country ALL PRIMARY NULL NULL NULL 183 1 SIMPLE City ref CountryCode CountryCode 3 world.Country.Code 1 $ pt-visual-explain explain.out |
Create a file containing the query and then parse the file while pt-visual-explain connects to the mysql instance, like the example shown below:
1 2 | $ echo "SELECT City.Name, Country.Name FROM City JOIN Country on (City.CountryCode=Country.Code)" > select.out $ pt-visual-explain -Dworld --connect select.out |
The tree structure below would be the result if you run any of the three methods shown above.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | JOIN +- Bookmark lookup | +- Table | | table City | | possible_keys CountryCode | +- Index lookup | key City->CountryCode | possible_keys CountryCode | key_len 3 | ref world.Country.Code | rows 1 +- Table scan rows 183 +- Table table Country possible_keys PRIMARY |
So how do we interpret the EXPLAIN plan? Simple. The query will do a table scan on Country table for 183 rows. For each row, it will do an index lookup into the City.CountryCode index with the value from Country.Code, then do a bookmark lookup into the City table.
Please read related posts about limits and errors, and Extended EXPLAIN here and here for more in-depth understanding of MySQL EXPLAIN plans.
And here’s another example using an EXPLAIN DELETE statement (works on MySQL version 5.6.3 and above) :
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | $ mysql -Dworld -e "EXPLAIN DELETE from Cities where CountryCode IN (select Code from Country where LifeExpectancy < 70.0)" | pt-visual-explain DEPENDENT SUBQUERY +- Filter with WHERE | +- Bookmark lookup | +- Table | | table Country | | possible_keys PRIMARY | +- Unique subquery | key Country->PRIMARY | possible_keys PRIMARY | key_len 3 | ref func | rows 1 +- Filter with WHERE +- Table scan rows 581 +- Table table Cities |
Alternatively, one can use – -format=dump option when parsing input into pt-visual-explain and it will create a Data::Dumper output which I think may be harder to understand but may be useable in some other cases.
Conclusion
The next time you want to check your execution plan information you can use pt-visual-explain to compare the result from the output of a regular EXPLAIN statement. You can also use MySQL Workbench, a GUI application that can produce beautified representation of the visual explain with colors, numbers on lines and other information.
For further reading please consult the sections under Modules and Algorithm on the pt-visual-explain documentation.
Other helpful information: pt-visual-explain is derived from Maatkit specifically mk-visual-explain. You can download Percona Toolkit from our download page – https://www.percona.com/downloads/ or get pt-visual-explain only by using $wget percona.com/get/pt-visual-explain.
Jericho,
Is pt-visual-explain able to handle MySQL 5.6 JSON explain output format which is able to provide more information than plain one ?
Peter,
Unfortunately after testing pt-visual-explain, it does not handle MySQL 5.6 JSON explain format using the –format option. The only supported formats available are: tree and dump.