How to Explain Your SQL EXPLAIN Queries

Share this article

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.

Frequently Asked Questions (FAQs) about SQL Explain Queries

What is the purpose of using SQL EXPLAIN queries?

SQL EXPLAIN queries are used to understand the execution plan of a SQL query. This is particularly useful when dealing with complex queries or large databases where performance can be a significant issue. By using EXPLAIN, you can see how the database would execute your query, including details about the use of indexes, join types, and the order of table scans. This information can help you optimize your queries for better performance.

How does SQL EXPLAIN help in query optimization?

SQL EXPLAIN provides a detailed breakdown of how the database plans to execute a given query. It shows the order in which tables are read, the type of read operation, possible indexes to choose, and the estimated number of rows to be read. By analyzing this information, you can identify potential bottlenecks or inefficiencies in your query and make necessary adjustments to improve performance.

What are the key elements in the output of an EXPLAIN query?

The output of an EXPLAIN query typically includes several key elements such as ‘id’, ‘select_type’, ‘table’, ‘type’, ‘possible_keys’, ‘key’, ‘key_len’, ‘ref’, ‘rows’, and ‘Extra’. Each of these elements provides specific information about the execution plan of the query, such as the type of join used, the number of rows to be scanned, the keys or indexes used, and additional details about the query execution.

How does the use of indexes affect the performance of a SQL query?

Indexes can significantly improve the performance of a SQL query by reducing the number of disk accesses required. When a query is executed, the database needs to scan the relevant tables to find the matching rows. If an index is available, the database can use it to directly access the required rows, thereby reducing the number of disk accesses. However, indexes also have a cost in terms of disk space and update time, so they should be used judiciously.

What is the difference between a ‘simple’ and a ‘complex’ select_type in an EXPLAIN output?

In an EXPLAIN output, the ‘select_type’ field indicates the type of SELECT query. A ‘simple’ select_type means that the query does not involve any subqueries or UNION operations. On the other hand, a ‘complex’ select_type indicates that the query involves subqueries, UNION operations, or other complex constructs.

How can I interpret the ‘rows’ field in an EXPLAIN output?

The ‘rows’ field in an EXPLAIN output provides an estimate of the number of rows that the database needs to scan to execute the query. This is an important indicator of the query’s performance. A high number of rows means that the database needs to read a large amount of data, which can be slow. By optimizing your query or using indexes, you can reduce the number of rows to be scanned and improve performance.

What does the ‘Extra’ field in an EXPLAIN output mean?

The ‘Extra’ field in an EXPLAIN output provides additional information about the query execution. It can include various messages such as ‘Using where’ (indicating that a WHERE clause is used), ‘Using index’ (indicating that an index is used), or ‘Using filesort’ (indicating that an extra pass is needed to sort the results). These messages can give you insights into the query’s performance and potential optimization strategies.

How can I use EXPLAIN with INSERT, UPDATE, or DELETE statements?

While EXPLAIN is commonly used with SELECT queries, it can also be used with INSERT, UPDATE, or DELETE statements. By prefixing these statements with EXPLAIN, you can see how the database plans to execute them. This can be useful for understanding the impact of these operations on the database performance and for identifying potential optimization strategies.

What is the difference between EXPLAIN and EXPLAIN ANALYZE in SQL?

EXPLAIN and EXPLAIN ANALYZE are two commands used to understand the execution plan of a SQL query. While EXPLAIN provides an estimated execution plan, EXPLAIN ANALYZE actually executes the query and provides a detailed report of the actual execution plan. This can be useful for understanding the actual performance of a query and for comparing it with the estimated plan.

How can I use the information from an EXPLAIN output to optimize my SQL queries?

The information from an EXPLAIN output can be used in several ways to optimize your SQL queries. For example, you can use it to identify inefficient join operations, unnecessary full table scans, or the misuse of indexes. By adjusting your queries based on this information, you can improve their performance and reduce the load on your database.

Craig BucklerCraig Buckler
View Author

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.

mariaDBmysqlsqltool
Share this article
Read Next
Get the freshest news and resources for developers, designers and digital creators in your inbox each week
Loading form