MK-VISUAL-EXPLAIN(1)

📦
version 7540 · 3 versions
, in fedora 43

💖 MK-VISUAL-EXPLAIN — the manual, but make it make sense 💖

okay so bestie, this is MK-VISUAL-EXPLAIN(1), straight out of the User Contributed Perl Documentation corner of the universe, and I'm gonna walk you through it like we're texting at 1am. grab a snack. 🍓

🏷️ NAME 🏷️

mk-visual-explain — it takes EXPLAIN output and formats it as a tree. that's the whole vibe, no cap. ✨

🎀 SYNOPSIS 🎀

Usage: mk-visual-explain [OPTION...] [FILE...]

mk-visual-explain transforms EXPLAIN output into a tree representation of the query plan. If you hand it a FILE, it reads the input from the file(s). And if there's no FILE, or if FILE is -, it reads standard input. 💅

Examples, because we love a demo:

mk-visual-explain <file_containing_explain_output>

mk-visual-explain -c <file_containing_query>

mysql -e "explain select * from mysql.user" | mk-visual-explain

⚠️ RISKS ⚠️

okay so this section exists to tell you about potential risks — whether known or unknown — of using this tool. the two main categories are risks from the nature of the tool itself (like, read-only tools vs. read-write tools) and risks from bugs.

mk-visual-explain is read-only and very low-risk. slay. 🫶

At the time of this release, we know of no bugs that could cause serious harm to users. periodt.

the authoritative source for updated info is always the online issue tracking system. issues that affect this tool get marked as such, and you can see the list of those issues right here: <http://www.maatkit.org/bugs/mk-visual-explain>.

also see "BUGS" for more on filing bugs and getting help. 💖

🔍 DESCRIPTION 🔍

mk-visual-explain reverse-engineers MySQL's EXPLAIN output into a query execution plan, and then formats it as a left-deep tree — which is literally the same way the plan is represented inside MySQL. sure, you could do this by hand, or read EXPLAIN's output directly, but bestie, that requires patience and expertise. lots of people find a tree representation way more understandable. it's giving clarity. 🦋

You can pipe input into mk-visual-explain, or specify a filename at the command line — including the magical - filename, which reads from standard input. it can do two things with the input: parse it for something that looks like EXPLAIN output, or connect to a MySQL instance and run EXPLAIN on the input.

When it's parsing its input, mk-visual-explain understands three formats:

  • tabular, like the one shown in the mysql command-line client
  • vertical, like the one created by using the \G line terminator in the mysql command-line client
  • tab separated

and it ignores any lines it doesn't know how to parse. 🙈

When it's executing the input, mk-visual-explain replaces everything in the input up to the first SELECT keyword with 'EXPLAIN SELECT,' and then executes the result. you must specify "--connect" to execute the input as a query.

Either way, it builds a tree from the result set and prints it to standard output. for this query here:

 select * from sakila.film_actor join sakila.film using(film_id);

mk-visual-explain generates this query plan:

 JOIN
 +- Bookmark lookup
 |  +- Table
 |  |  table          film_actor
 |  |  possible_keys  idx_fk_film_id
 |  +- Index lookup
 |     key            film_actor->idx_fk_film_id
 |     possible_keys  idx_fk_film_id
 |     key_len        2
 |     ref            sakila.film.film_id
 |     rows           2
 +- Table scan
    rows           952
    +- Table
       table          film
       possible_keys  PRIMARY

The query plan is left-deep, depth-first search, and the tree's root is the output node — the last step in the execution plan. in other words, read it like this:

  1. Table scan the 'film' table, which accesses an estimated 952 rows.
  2. For each row, find matching rows by doing an index lookup into the film_actor->idx_fk_film_id index with the value from sakila.film.film_id, then a bookmark lookup into the film_actor table.

For more info on how to read EXPLAIN output, please see <http://dev.mysql.com/doc/en/explain.html>, and this talk titled "Query Optimizer Internals and What's New in the MySQL 5.2 Optimizer," from Timour Katchaounov, one of the MySQL developers: <http://maatkit.org/presentations/katchaounov_timour.pdf>. 📚

🧩 MODULES 🧩

okay so this program is actually a runnable module, not just an ordinary Perl script. in fact, there are two modules embedded in it. this makes unit testing easy, but it also makes it easy for you to use the parsing and tree-building functionality if you want. bestie perks. 💅

The ExplainParser package accepts a string and parses whatever it thinks looks like EXPLAIN output from it. the synopsis is as follows:

 require "mk-visual-explain";
 my $p    = ExplainParser->new();
 my $rows = $p->parse("some text");
 # $rows is an arrayref of hashrefs.

The ExplainTree package accepts a set of rows and turns it into a tree. for convenience, you can also have it delegate to ExplainParser and parse text for you. here's the synopsis:

 require "mk-visual-explain";
 my $e      = ExplainTree->new();
 my $tree   = $e->parse("some text", \%options);
 my $output = $e->pretty_print($tree);
 print $tree;

🌳 ALGORITHM 🌳

this section explains the algorithm that converts EXPLAIN into a tree. you may be interested in reading this if you wanna understand EXPLAIN more fully, or if you're trying to figure out how this works — but otherwise this section will probably not make your life richer. just being honest. 😭

The tree can be built by examining the id, select_type, and table columns of each row. here's what I know about them:

The id column is the sequential number of the select. this does not indicate nesting; it just comes from counting SELECT from the left of the SQL statement. it's like capturing parentheses in a regular expression. a UNION RESULT row doesn't have an id, because it isn't a SELECT. the source code actually refers to UNIONs as a fake_lex, as I recall.

If two adjacent rows have the same id value, they are joined with the standard single-sweep multi-join method.

The select_type column tells you a) that a new sub-scope has opened b) what kind of relationship the row has to the previous row c) what kind of operation the row represents.

  • SIMPLE means there are no subqueries or unions in the whole query.
  • PRIMARY means there are, but this is the outermost SELECT.
  • [DEPENDENT] UNION means this result is UNIONed with the previous result (not row; a result might encompass more than one row).
  • UNION RESULT terminates a set of UNIONed results.
  • [DEPENDENT|UNCACHEABLE] SUBQUERY means a new sub-scope is opening. this is the kind of subquery that happens in a WHERE clause, SELECT list or whatnot; it does not return a so-called "derived table."
  • DERIVED is a subquery in the FROM clause.

Tables that are JOINed all have the same select_type. for example, if you JOIN three tables inside a dependent subquery, they'll all say the same thing: DEPENDENT SUBQUERY.

The table column usually specifies the table name or alias, but may also say <derivedN> or <unionN,N...N>. if it says <derivedN>, the row represents an access to the temporary table that holds the result of the subquery whose id is N. if it says <unionN,..N> it's the same thing, but it refers to the results it UNIONs together.

Finally, order matters. if a row's id is less than the one before it, I think that means it is dependent on something other than the one before it. for example:

 explain select
    (select 1 from sakila.film),
    (select 2 from sakila.film_actor),
    (select 3 from sakila.actor);

 | id | select_type | table      |
 +----+-------------+------------+
 |  1 | PRIMARY     | NULL       |
 |  4 | SUBQUERY    | actor      |
 |  3 | SUBQUERY    | film_actor |
 |  2 | SUBQUERY    | film       |

If the results were in order 2-3-4, I think that would mean 3 is a subquery of 2, 4 is a subquery of 3. as it is, this means 4 is a subquery of the nearest previous recent row with a smaller id, which is 1. likewise for 3 and 2.

This structure is hard to programatically build into a tree for the same reason it's hard to understand by inspection: there are both forward and backward references. <derivedN> is a forward reference to selectN, while <unionM,N> is a backward reference to selectM and selectN. that makes recursion and other tree-building algorithms hard to get right (NOTE: after implementation, I now see how it would be possible to deal with both forward and backward references, but I have no motivation to change something that works). consider the following:

 select * from (
    select 1 from sakila.actor as actor_1
    union
    select 1 from sakila.actor as actor_2
 ) as der_1
 union
 select * from (
    select 1 from sakila.actor as actor_3
    union all
    select 1 from sakila.actor as actor_4
 ) as der_2;

 | id   | select_type  | table      |
 +------+--------------+------------+
 |  1   | PRIMARY      | <derived2> |
 |  2   | DERIVED      | actor_1    |
 |  3   | UNION        | actor_2    |
 | NULL | UNION RESULT | <union2,3> |
 |  4   | UNION        | <derived5> |
 |  5   | DERIVED      | actor_3    |
 |  6   | UNION        | actor_4    |
 | NULL | UNION RESULT | <union5,6> |
 | NULL | UNION RESULT | <union1,4> |

This would be a lot easier to work with if it looked like this (I've bracketed the id on rows I moved):

 | id   | select_type  | table      |
 +------+--------------+------------+
 | [1]  | UNION RESULT | <union1,4> |
 |  1   | PRIMARY      | <derived2> |
 | [2]  | UNION RESULT | <union2,3> |
 |  2   | DERIVED      | actor_1    |
 |  3   | UNION        | actor_2    |
 |  4   | UNION        | <derived5> |
 | [5]  | UNION RESULT | <union5,6> |
 |  5   | DERIVED      | actor_3    |
 |  6   | UNION        | actor_4    |

In fact, why not re-number all the ids, so the PRIMARY row becomes 2, and so on? that would make it even easier to read. unfortunately that would also have the effect of destroying the meaning of the id column, which I think is important to preserve in the final tree. also, though it makes it easier to read, it doesn't make it easier to manipulate programmatically; so it's fine to leave them numbered as they are.

The goal of re-ordering is to make it easier to figure out which rows are children of which rows in the execution plan. given the reordered list and some row whose table is <union...> or <derived>, it is easy to find the beginning of the slice of rows that should be child nodes in the tree: you just look for the first row whose ID is the same as the first number in the table.

The next question is how to find the last row that should be a child node of a UNION or DERIVED. I'll start with DERIVED, because the solution makes UNION easy. 🧚

Okay so like, think about how MySQL numbers the SELECTs — it goes sequentially, left-to-right, according to where they sit in the SQL. And since a DERIVED table wraps <em>everything</em> inside it up in a scope (which lowkey becomes a temporary table), there are only two things you gotta think about: its child subqueries and unions (if it has any), and its next siblings in the scope that encloses it. Its children will all have an id greater than it does, by definition, so any later rows with a smaller id = scope terminated, bye. ✨

Here's an example bestie. The middle derived table here has a subquery and a UNION so it's a lil more complex, for ✨demonstration purposes✨.

explain select 1
from (
   select film_id from sakila.film limit 1
) as der_1
join (
   select film_id, actor_id, (select count(*) from sakila.rental) as r
   from sakila.film_actor limit 1
   union all
   select 1, 1, 1 from sakila.film_actor as dummy
) as der_2 using (film_id)
join (
   select actor_id from sakila.actor limit 1
) as der_3 using (actor_id);

And here's what EXPLAIN spits out:

| id   | select_type  | table      |
|  1   | PRIMARY      | <derived2> |
|  1   | PRIMARY      | <derived6> |
|  1   | PRIMARY      | <derived3> |
|  6   | DERIVED      | actor      |
|  3   | DERIVED      | film_actor |
|  4   | SUBQUERY     | rental     |
|  5   | UNION        | dummy      |
| NULL | UNION RESULT | <union3,5> |
|  2   | DERIVED      | film       |

So the siblings all have id 1, and the middle one I care about is derived3. (Side note: MySQL does not execute them in the order I defined them in, which is totally fine, we're not mad at it. 🙈) Now peep this — MySQL prints out the rows in the opposite order I defined the subqueries in: 6, 3, 2. It always seems to do this, and like, there might be other methods of finding the scope boundaries (looking for the lower boundary of the next largest sibling, for example), but this is a good enough heuristic. I'm forced to rely on it for non-DERIVED subqueries, so I rely on it here too. Therefore I decide that everything greater than or equal to 3 belongs to the DERIVED scope. 💅

The rule for UNION is simple: they consume the entire enclosing scope. And to find the component parts of each one, you find each part's beginning as referred to in the <unionN,...> definition, and its end is either just before the next one, or — if it's the last part — the end is the end of the scope.

This is only simple because UNION consumes the entire scope, which is either the entire statement or the scope of a DERIVED table. Why? Because a UNION cannot be a sibling of another UNION or a table, DERIVED or not. (Try writing such a statement if you don't see it intuitively, bestie, go ahead, I'll wait. 🍓) Therefore you can just find the enclosing scope's boundaries and the rest is easy. Notice in the example above, the UNION is over <union3,5>, which includes the row with id 4 — it includes every row between 3 and 5.

Finally, there are non-derived subqueries to deal with as well. In this case I can't look at siblings to find the end of the scope like I did for DERIVED. I have to trust that MySQL executes depth-first. Here's an example:

explain
select actor_id,
(
   select count(film_id)
   + (select count(*) from sakila.film)
   from sakila.film join sakila.film_actor using(film_id)
   where exists(
      select * from sakila.actor
      where sakila.actor.actor_id = sakila.film_actor.actor_id
   )
)
from sakila.actor;

| id | select_type        | table      |
|  1 | PRIMARY            | actor      |
|  2 | SUBQUERY           | film       |
|  2 | SUBQUERY           | film_actor |
|  4 | DEPENDENT SUBQUERY | actor      |
|  3 | SUBQUERY           | film       |

In order, the tree should be built like this:

  • See row 1.
  • See row 2. It's a higher id than 1, so it's a subquery, along with every other row whose id is greater than 2.
  • Inside this scope, see 2 and 2 and JOIN them. See 4. It's a higher id than 2, so it's again a subquery; recurse. After that, see 3, which is also higher; recurse.

But the only reason the nested subquery didn't include select 3 is because select 4 came first. In other words, if EXPLAIN looked like this,

| id | select_type        | table      |
|  1 | PRIMARY            | actor      |
|  2 | SUBQUERY           | film       |
|  2 | SUBQUERY           | film_actor |
|  3 | SUBQUERY           | film       |
|  4 | DEPENDENT SUBQUERY | actor      |

I would be forced to assume, upon seeing select 3, that select 4 is a subquery of it, rather than just being the next sibling in the enclosing scope. If this is ever wrong, then the algorithm is wrong, and ngl I don't see what could be done about it. 💀

UNION is a lil more complicated than just "the entire scope is a UNION," because the UNION might itself be inside an enclosing scope that's only indicated by the first item inside the UNION. And there are only three kinds of enclosing scopes: UNION, DERIVED, and SUBQUERY. A UNION can't enclose a UNION, and a DERIVED has its own "scope markers," but a SUBQUERY can wholly enclose a UNION, like this strange example on the empty table t1:

explain select * from t1 where not exists(
   (select t11.i from t1 t11) union (select t12.i from t1 t12));

|   id | select_type  | table      | Extra                          |
+------+--------------+------------+--------------------------------+
|    1 | PRIMARY      | t1         | const row not found            |
|    2 | SUBQUERY     | NULL       | No tables used                 |
|    3 | SUBQUERY     | NULL       | no matching row in const table |
|    4 | UNION        | t12        | const row not found            |
| NULL | UNION RESULT | <union2,4> |                                |

The UNION's backward references might make it look like the UNION encloses the subquery, but studying the query makes it clear this isn't the case. So when a UNION's first row says SUBQUERY, it's this special case. 🦋

By the way — I don't fully understand this query plan. There are 4 numbered SELECT in the plan, but only 3 in the query. The parens around the UNIONs are meaningful. Removing them will make the EXPLAIN different. Please tell me how and why this works if you know. 🥺

Armed with this knowledge, it's possible to use recursion to turn the parent-child relationship between all the rows into a tree representing the execution plan.

MySQL prints the rows in execution order, even the forward and backward references. At any given scope, the rows are processed as a left-deep tree. MySQL does not do "bushy" execution plans. It begins with a table, finds a matching row in the next table, and continues till the last table, when it emits a row. When it runs out, it backtracks till it can find the next row and repeats. There are subtleties of course, but this is the basic plan. This is why MySQL transforms all RIGHT OUTER JOINs into LEFT OUTER JOINs and cannot do FULL OUTER JOIN.

This means in any given scope, say

| id   | select_type  | table      |
|  1   | SIMPLE       | tbl1       |
|  1   | SIMPLE       | tbl2       |
|  1   | SIMPLE       | tbl3       |

the execution plan looks like a depth-first traversal of this tree:

       JOIN
      /    \
    JOIN  tbl3
   /    \
 tbl1   tbl2

The JOIN might not be a JOIN. It might be a subquery, for example. This comes from the type column of EXPLAIN. The documentation says this is a "join type," but I think "access type" is more accurate, because it's "how MySQL accesses rows." 👑

mk-visual-explain decorates the tree significantly more than just turning rows into nodes. Each node may get a series of transformations that turn it into a subtree of more than one node. For example, an index scan not marked with Using index must do a bookmark lookup into the table rows; that is a three-node subtree. However, after the above node-ordering and scoping stuff, the rest of the process is pretty simple. ✨

✨ OPTIONS ✨

okay so this tool takes extra command-line arguments, bestie. the "SYNOPSIS" and the usage info have the deets if you need them. 💖

  • --ask-pass — prompts you for a password when it's connecting to MySQL. so polite of it. 🔐
  • --charset — short form: -A; type: string. this is your default character set. here's the tea: if the value is utf8, it sets Perl's binmode on STDOUT to utf8, passes the mysql_enable_utf8 option to DBD::mysql, AND runs SET NAMES UTF8 after connecting to MySQL. any other value sets binmode on STDOUT without the utf8 layer, and runs SET NAMES after connecting to MySQL. so like, utf8 gets the whole glow-up, everything else gets the basic treatment. 🦋
  • --clustered-pk — assume that PRIMARY KEY index accesses don't need to do a bookmark lookup to retrieve rows. this is the case for InnoDB. basically it's telling the tool "yeah bestie, the rows are right there, no scavenger hunt needed." 👑
  • --config — type: Array. read this comma-separated list of config files. ⚠️ if you specify it, it has to be the first option on the command line. first or it's not happening, periodt.
  • --connect — treat input as a query, and get the EXPLAIN output by connecting to a MySQL instance and running EXPLAIN on that query. when you give this option, mk-visual-explain uses the other connection-specific options like --user to connect to the MySQL instance. and if you have a .my.cnf file, it'll read it, so you might not need to specify any connection-specific options at all. iconic, honestly. 🔥
  • --database — short form: -D; type: string. connect to this database.
  • --defaults-file — short form: -F; type: string. only read mysql options from the given file. 📌 you must give an absolute pathname. no relative paths, no shortcuts, it wants the full address.
  • --format — type: string; default: tree. sets the output format. the default is a terse pretty-printed tree. the valid values are:
    • tree — pretty-printed terse tree.
    • dump — Data::Dumper output (see Data::Dumper for more).

so like, tree for the cute readable vibe, dump when you want the raw data dump energy. 🌈

  • --help — show help and exit. the classic. 🙈
  • --host — short form: -h; type: string. connect to host.
  • --password — short form: -p; type: string. the password to use when connecting.
  • --pid — type: string. create the given PID file. the file contains the process ID of the script, and the PID file is removed when the script exits. before starting, the script checks if the PID file already exists. if it doesn't, then the script creates it and writes its own PID to it. if it does exist, then the script checks the following: if the file contains a PID and a process is running with that PID, then the script dies 💀; or, if there's no process running with that PID, then the script overwrites the file with its own PID and starts; else, if the file contains no PID, then the script dies. so many ways to die, but that's just the PID file life. 😭
  • --port — short form: -P; type: int. port number to use for connection.
  • --set-vars — type: string; default: wait_timeout=10000. set these MySQL variables. immediately after connecting to MySQL, this string gets appended to SET and executed. so it's like, "hi MySQL, before we do anything, let me just fix a few things about you." 💅
  • --socket — short form: -S; type: string. socket file to use for connection.
  • --user — short form: -u; type: string. user for login if not current user.
  • --version — show version and exit. ⭐

🧚 DSN OPTIONS 🧚

these DSN options are used to create a DSN, bestie. each option is given like option=value. ⚠️ the options are case-sensitive, so P and p are not the same option — do not play with them. there cannot be whitespace before or after the =, and if the value contains whitespace it must be quoted. DSN options are comma-separated. see the maatkit manpage for full details. 📖

  • A — dsn: charset; copy: yes. default character set.
  • D — dsn: database; copy: yes. default database.
  • F — dsn: mysql_read_default_file; copy: yes. only read default options from the given file.
  • h — dsn: host; copy: yes. connect to host.
  • p — dsn: password; copy: yes. password to use when connecting.
  • P — dsn: port; copy: yes. port number to use for connection.
  • S — dsn: mysql_socket; copy: yes. socket file to use for connection.
  • u — dsn: user; copy: yes. user for login if not current user.

🌸 DOWNLOADING 🌸

you can download Maatkit from Google Code at <http://code.google.com/p/maatkit/>, or you can get any of the tools easily with a command like the following, bestie:

wget http://www.maatkit.org/get/toolname
or
wget http://www.maatkit.org/trunk/toolname

where "toolname" can be replaced with the name (or fragment of a name) of any of the Maatkit tools. once downloaded, they're ready to run; no installation is needed. the first URL gets the latest released version of the tool, and the second gets the latest trunk code from Subversion. so easy it's giving effortless. ✨

🫶 ENVIRONMENT 🫶

the environment variable MKDEBUG enables verbose debugging output in all of the Maatkit tools:

MKDEBUG=1 mk-....

turn it on and the tool becomes a total over-sharer. 💜

👑 SYSTEM REQUIREMENTS 👑

you need Perl, DBI, DBD::mysql, and some core packages that ought to be installed in any reasonably new version of Perl. that's it. that's the list. 🎀

💀 BUGS 💀

for a list of known bugs see <http://www.maatkit.org/bugs/mk-visual-explain>.

please use Google Code Issues and Groups to report bugs or request support: <http://code.google.com/p/maatkit/>. you can also join #maatkit on Freenode to discuss Maatkit.

please include the complete command-line used to reproduce the problem you're seeing, the version of all MySQL servers involved, the complete output of the tool when run with --version, and if possible, debugging output produced by running with the MKDEBUG=1 environment variable. 📝 like, give them everything, don't make them guess. the more receipts the better. 🧾

this program is copyright 2007-2011 Baron Schwartz. feedback and improvements are welcome. 🫶

THIS PROGRAM IS PROVIDED "AS IS" AND WITHOUT ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, WITHOUT LIMITATION, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.

this program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation, version 2; OR the Perl Artistic License. on UNIX and similar systems, you can issue man perlgpl or man perlartistic to read these licenses.

you should have received a copy of the GNU General Public License along with this program; if not, write to the Free Software Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA.

🦋 SEE ALSO 🦋

see also mk-query-profiler. your other fave. 💅

⭐ AUTHOR ⭐

Baron "Xaprb" Schwartz — the icon, the legend. 👑

🎀 ABOUT MAATKIT 🎀

this tool is part of Maatkit, a toolkit for power users of MySQL. Maatkit was created by Baron Schwartz; Baron and Daniel Nichter are the primary code contributors. both are employed by Percona. financial support for Maatkit development is primarily provided by Percona and its clients. so yeah, the whole thing is a group effort and it ate. 🔥

💖 VERSION 💖

this manual page documents Ver 1.0.22 Distrib 7540 $Revision: 7477 $.

okay bestie that's the whole page, we did it, we survived the lore dump 🫶 this lil manual page is brought to you by perl v5.42.0, dated 2025-07-24, filed under MK-VISUAL-EXPLAIN(1) — and honestly? iconic of it. go forth and explain some queries. no cap. 💅✨

📦 where this page comes from