Key Takeaways
- The EXPLAIN command provided by MySQL and MariaDB can be used to analyze SQL queries and identify potential performance issues. It describes how a SELECT will be processed, including information about JOINS, and estimates the number of table rows filtered by the condition when using EXPLAIN EXTENDED.
- The MariaDB EXPLAIN Analyzer is a helpful tool that can interpret the output of an EXPLAIN command in plain English. This can be particularly useful when trying to decipher complex query execution plans and identify areas for optimization.
- While using the EXPLAIN command with SELECT queries is common, it can also be used with INSERT, UPDATE, or DELETE statements. This can help understand the impact of these operations on database performance and identify potential optimization strategies.
EXPLAIN
command which can analyze your queries and detect potential performance issues:
EXPLAIN
describes how aSELECT
will be processed including information aboutJOINS
.EXPLAIN EXTENDED
provides additional information and estimates the number of table rows that are filtered by the condition.
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 anEXPLAIN
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;
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 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.