Skip to main content

How to Explain Your SQL EXPLAIN Queries

By Craig Buckler



Free JavaScript Book!

Write powerful, clean and maintainable JavaScript.

RRP $11.95

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.


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:

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


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


  • [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.

Craig is a freelance UK web consultant who built his first page for IE2.0 in 1995. Since that time he's been advocating standards, accessibility, and best-practice HTML5 techniques. He's created enterprise specifications, websites and online applications for companies and organisations including the UK Parliament, the European Parliament, the Department of Energy & Climate Change, Microsoft, and more. He's written more than 1,000 articles for SitePoint and you can find him @craigbuckler.

New books out now!

Get practical advice to start your career in programming!

Master complex transitions, transformations and animations in CSS!

Latest Remote Jobs