MySQL for Developers

Understanding explain in MySQL

As a MySQL developer, it's crucial to understand the performance of your queries. MySQL provides a tool called explain that can help you understand how your queries are being executed. In this post, we will dive deeper into explain and explore the various formats it offers.

To explain a query, you simply prefix your query with explain. MySQL will then output the query execution plan. Let's consider an example:

explain select * from people where first_name = "Aaron"

In the above query, MySQL will output information about the query plan, including any index used and an estimate of the number of rows being accessed.

Different formats of explain

The explain statement provides several formats that you can use to analyze your queries in more detail. Some of the commonly used explain formats are tree, JSON, and EXPLAIN ANALYZE.

Explain tree format

The tree format is useful for providing more detail into the execution plan in a nested tree structure. You can specify this format by adding format=tree at the beginning of the explain statement.

For example, consider the following query:

explain format=tree select * from people where first_name = "Aaron"

We're providing with the following output, which estimates the cost of each part of the query. (This query only has one part, so it doesn't look too much like a tree!)

-> Index lookup on people using multi (first_name='Aaron')  (cost=198.00 rows=180)

Explain JSON format

The JSON format provides a more detailed view of the same information as provided in the tree format. You can specify this format by adding format=json at the beginning of the explain statement.

In the JSON format, MySQL provides information about the query's index usage, key parts, and query cost in a machine-readable, JSON format.

Explain analyze format

EXPLAIN ANALYZE actually runs the query and provides detailed statistics on the query's execution plan. It's important to note that this format actually runs the query, so it should be used with caution.

If you use the EXPLAIN ANALYZE format, MySQL will provide the detailed statistics that include the actual execution time and number of records read. If you're working with queries involving joins or subqueries, these statistics can be invaluable for understanding where the query is spending its time.


Explain in MySQL can significantly help optimize the performance of queries by providing insight into how they are executed. In this video, we discussed the different formats of explain that are available, including tree, JSON, and analyze formats.

While the various formats may seem overwhelming, it's essential to understand the output of the basic explain query. Even if you're new to MySQL, understanding the basic explain output will give you an advantage over most application developers.

About this lesson

The EXPLAIN ANALYZE format gives you a lot more detail to work with as compared to the traditional EXPLAIN output. It can also be a little bit intimidating.

Closed captioned

Meet your instructor, Aaron Francis

Developer Educator at PlanetScale

I’m a dad to one-year-old twins and a software developer with a keen interest in processes and automation.

Feedback or questions? Reach out to our team at

By submitting your email, you agree to the processing of your personal information by PlanetScale as described in the Privacy Policy.