Blackmjck

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…

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.

The post How to Explain Your SQL EXPLAIN Queries appeared first on SitePoint.