How to Read & Interpret MySQL EXPLAIN Plans (+ Example)

MySQL EXPLAIN plans provide useful information about how the MySQL optimizer executes queries. This can be invaluable for troubleshooting query performance issues and improving overall database optimization.

In this article, we will discuss how to read and interpret an EXPLAIN plan. We’ll also look at a MySQL EXPLAIN plan example so that you can get an in-depth understanding of how to use the output.

What is EXPLAIN in MySQL?

EXPLAIN is a MySQL command that shows how the query optimizer processes a SQL statement. It displays information such as which tables are being accessed, join types, and estimated execution times.

When the MySQL optimizer creates a query execution plan, it makes use of the EXPLAIN keyword. This will output information about how MySQL plans to execute the query and what type of optimization techniques are being used.

MySQL EXPLAIN Plan Output

Let’s look at a sample output from an EXPLAIN plan:

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

Each row of the EXPLAIN plan output contains the following information:

• id: The unique identifier for the query
• select_type: The type of query being executed (e.g. SELECT, INSERT, etc.)
• table: The name of the table(s) being accessed
• type: The join type used to access the data (e.g., LEFT JOIN, INNER JOIN, etc.)
• possible_keys: Any relevant indexes that could be used by the optimizer
• key: The index(es) chosen by the query optimizer (if any)
• key_len: The length of the chosen index(es) (if any)
• ref: Any columns in the WHERE clause used to further limit data retrieval
• rows: The estimated number of records that will be retrieved
• Extra: Any extra information such as whether filesort or temporary tables are being used

MySQL EXPLAIN Plan Example

Now let’s look at a sample query and its output. In this example, we are selecting all records from a user table where the user_id is equal to 5.

SELECT * FROM user WHERE user_id = 5;
+----+-------------+-------+------+--------------+------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra |
+----+-------------+-------+------+--------------+------+---------+-------+------+-------+
| 1  | SIMPLE      | user  | ALL  | user_id      | NULL | NULL    | const | 4       |       |
+----+-------------+-------+------+--------------+------+---------+-------+------+-------+

We can interpret this output as follows:

• The query is a simple select (select_type = SIMPLE) from the table (table = user).
• The optimizer is using a full table scan (type = ALL) to retrieve the data.
• The ‘user_id’ field is indexed and could be used, but the optimizer has decided not to use it (possible_keys = user_id, key = NULL).
• The WHERE clause condition ‘user_id = 5’ is being used to limit data retrieval (ref = const).
• The optimizer estimates that only four records will be returned (rows = 4).

By interpreting the output of an EXPLAIN plan, you can gain valuable insight into how MySQL is executing your queries and make informed decisions on how best to optimize them.

High Performance MySQL, 4th Edition

With High Performance MySQL, you'll learn advanced techniques for everything from setting service-level objectives to designing schemas, indexes, and queries to tuning your server, operating system, and hardware to achieve your platform's full potential.

Buy now

How can EXPLAIN help me write better SQL?

An EXPLAIN plan can you can make informed decisions about how to improve the performance of your queries.

Knowing which indexes are being used and whether or not a full table scan is happening can help you choose the most optimal query structure. Additionally, examining the estimated row counts for each part of your query can help determine if an index is needed or if a smaller data set could be used.

EXPLAIN plans can also help identify any unnecessary processing, such as sorting that could be eliminated for improved performance. You can ensure your SQL queries are optimized for maximum performance.

We hope this article has helped provide insight into how to read and interpret an EXPLAIN plan with a MySQL EXPLAIN plan example.

With this knowledge, you can start optimizing your database queries and get the most out of your data.