How to Explain Your SQL EXPLAIN Queries

The cool kids may have migrated to NoSQL but, for many of us, relational databases remain an important part of everyday web development. I’m also willing to bet relational databases will still be around in ten years time. Despite our familiarity, SQL syntax is a dark art. The only person who understands SQL is that strange bloke with a beard and sandals who mutters to himself in the corner of your office. Everyone else uses the force and spends many frustrated hours looking aimlessly at broken SELECT queries.

MySQL and MariaDB provide a useful EXPLAIN command which can analyze your queries and detect potential performance issues:

  • EXPLAIN describes how a SELECT will be processed including information about JOINS.
  • EXPLAIN EXTENDED provides additional information and estimates the number of table rows that are filtered by the condition.

The command is simply added to the start of your query, e.g.

EXPLAIN SELECT * FROM `user`;

The output result:

+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | user  | ALL  | NULL          | NULL | NULL    | NULL |    4 |       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+

Erm — what does that all mean?

Fortunately, those nice people at MariaDB have provided a useful analyzer to explain your EXPLAINs:
https://mariadb.org/explain_analyzer/

You can paste the resulting table into the Submit a new Explain and hit the Analyze button…

SQL EXPLAIN Analyzer

Click any bold underlined text for further information about the EXPLAIN result.

Extracting EXPLAIN Tables

Unfortunately, extracting an EXPLAIN table can be a little tricky. If you’re using the mysql> command line interface, the result can be wrapped:

+----+-------------+-------+------+---------------+------+---------+------+-----
-+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows
 | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+-----
-+-------------+
|  1 | SIMPLE      | user  | ALL  | NULL          | NULL | NULL    | NULL |    4
 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+-----
-+-------------+

Copying directly from the console into the Analyzer won’t work unless you remove the necessary line breaks first. You may be able to increase the width of your console window but there’s no guarantee every result would fit.

A better alternative is to output the result to a text file and copy the EXPLAIN table from there. If you’re using the MySQL command line, the tee command will redirect all console output to a named file, e.g.

mysql> tee out.txt

(Note that file paths should use ‘/’ forward slashes even on Windows.)

Alternatively, you can use a \T command line parameter when launching mysql:

> mysql \T "out.txt"

Or, if you’re feeling particularly brave, you can direct all EXPLAIN output to a file using a single command:

> mysql -u [dbuser] -p[dbpass] -e "[query]" > out.txt

where:

  • [dbuser] is the database user’s name
  • [dbpass] is the database user’s password (there is no space between the -p and the password)
  • [query] is the SQL statement, e.g. USE mydatabase; EXPLAIN SELECT * FROM mytable;

In this case, the output filename path should use your OS standard, i.e. ‘\’ on Windows and ‘/’ on Mac or Linux.

The Explain Analyzer is a great tool which explains EXPLAIN in plain-English. You’ll soon become the SQL guru in your team … start growing that beard.

Free book: Jump Start HTML5 Basics

Grab a free copy of one our latest ebooks! Packed with hints and tips on HTML5's most powerful new features.

  • Anonymous

    “Cool kids”, eh? You don’t have to be a bearded sandal wearing dude to actually learn something, you just have to do your homework. SQL is so easy to learn that any dev that can’t use it should probably go get a job flipping burgers. Likewise, if you use ORM because SQL is too hard, you might be the french fry guy.

    • Arturo A.

      You sir, made my day.

    • Anonymous

      Oscar — please don’t take it too literally. The stereotypical references were not serious and apologies if you happen to be a bearded, sandal wearing, SQL-loving dude…

      The rules of SQL are not hard. Similarly, the rules of chess are even easier … but that doesn’t make everyone a grandmaster. In my experience, few developers claim to be SQL experts or use it as effectively as they could. They know the syntax but moving beyond basic queries into complex data modelling and extraction is not something everyone is able or willing to do. Look through any open source project and you’ll find many excerpts which handle data manipulation in code rather than handing the task to the database.

      I’ve been using SQL for many years, but I’m still learning and it takes a little lateral thought and experimentation to create complex queries. Few other languages require the same cerebral dexterity.

  • Oscar Alderete

    SQL is easy to understand when you use ANSI join syntax. Try it!

  • Raymond Porrata

    Several other vendors also provide the ‘Query Plan’ (i.e.: sqlserver using SET STATISTICS PROFILE ON; oracle like MySQL EXPLAIN PLAN, etc.) based explanations including the quality of the query in terms of its use of indexes, scanning, io throughput, etc. I mention it since it is ignore as a key tool to help with the ‘this query is too slow’ problem. Thanks for this and all the others you manage to dish out – I do love to see what others are doing and how they do it… I think it lends perspective. Enjoy!