Log in
Get Started
Log in
Get Started

MySQL EXPLAIN: How to Read Execution Plans

Written By ROMAN AGABEKOV | APRIL 11, 2025

Last update | SEP 15, 2026

Slow queries can quietly erode performance, which frustrates users and strains your system. Fortunately, MySQL offers EXPLAIN, a diagnostic tool that reveals how your queries execute. Think of it as a window into your database engine’s decision-making process.

With EXPLAIN, you can figure out exactly why a query is underperforming. Maybe it’s looking at too many rows, ignoring indexes, or struggling with joins. This understanding is key for developers and database administrators who are aiming to optimize performance.

How to Use EXPLAIN

To use EXPLAIN, simply place it before your query. It works with SELECT, INSERT, UPDATE, or DELETE statements. For instance:
EXPLAIN SELECT name, email FROM customers WHERE age > 30;
This will return the query execution plan in table format.
For a more comprehensive output, try the JSON format:
EXPLAIN FORMAT=JSON SELECT first_name, email FROM customers WHERE age > 25;
This provides a structured output with extra details, such as cost estimates, which can deepen your analysis.

How to Read EXPLAIN Table output

The EXPLAIN command outputs a table where each column highlights specific aspects of the query execution. When you combine these columns, you gain a detailed snapshot of the database’s strategy and where you can make optimizations.

5 Questions to Ask When Interpreting the Results of EXPLAIN

To interpret EXPLAIN, you need to ask the right questions.

1. What is the Query Structure (id and select_type)?

Start by looking at id and select_type to get a sense of how complicated your query is. Do you see just one id (like 1) alongside SIMPLE, meaning it’s a basic operation? Or are there multiple id numbers (say, 1 and 2) or words like SUBQUERY or DERIVED, pointing to something with nested steps or multiple parts?

A complex structure doesn’t guarantee slowness, but it’s a cue to check for simplification opportunities, like flattening a subquery into a join.

2. What is MySQL’s Approach to Accessing Data (type)?

ALL indicates a table scan; index indicates a full index scan. A scan can be appropriate for a small table or a query that needs much of it. Compare the chosen access path with the query's filters, required rows and measured performance rather than treating the access type alone as a diagnosis.

3. Are Indexes Being Leveraged Effectively (possible_keys, key, key_len)?

Compare possible_keys with key. If the latter is NULL despite options in possible_keys, why did MySQL skip them? Was it due to poor selectivity, small table size, or because the query was written in a way that made the index unusable?

4. What is the Extent of Row Scanning and Filtering? (rows, filtered)

Compare the estimated rows with the result the query needs to produce. Fewer unnecessary reads can help, but aggregates and broad result sets may legitimately examine many rows. The rows and filtered values are estimates, not measured execution counts.

5. Is MySQL Doing Unnecessary Extra Work (Extra)?

Using index indicates a covering index access. Using filesort indicates an additional sort, and Using temporary indicates use of a temporary table. These are investigation clues, not proof that the query is slow; check their cost for the actual workload.

Diagnosing Query Performance Issues with EXPLAIN

Once you’ve got the basics down, it’s time to use what you’ve learned to uncover and address any inefficiencies in your laggy queries:

1. Prevent Full Scans

ALL indicates a table scan; index indicates a full index scan. A scan can be appropriate for a small table or a query that needs much of it. Compare the chosen access path with the query's filters, required rows and measured performance rather than treating the access type alone as a diagnosis.

2. Address Unused Indexes Caused by Pattern Matching

key = NULL means MySQL isn’t leveraging an index, even if one does exist. This can happen in situations where query conditions don’t align with the available indexes. For example:
SELECT email FROM users WHERE email LIKE '%yahoo.com';
A leading wildcard prevents this predicate from defining a B-tree range lookup. Removing the wildcard changes which rows match. FULLTEXT search also has different matching rules: it searches words, not an email suffix. Keep the required matching semantics when evaluating another search design.

3. Optimize Inefficient Joins

  • Check whether an existing index supports the join columns and whether the column types are compatible. An ordinary nonunique index does not by itself enable eq_ref: that access type requires equality lookup using all parts of a PRIMARY KEY or UNIQUE NOT NULL index. The optimizer still chooses the access plan.

4. Improve Sorting and Filtering

If EXPLAIN shows Using filesort in the Extra column, MySQL is sorting data in memory or on disk. This is a resource-intensive step that often happens with ORDER BY or GROUP BY clauses when no index supports the operation.

You can address this for a query like EXPLAIN SELECT sale_id FROM sales ORDER BY sale_date; by adding an index:
CREATE INDEX idx_sale_date ON sales(sale_date);

5. Avoid Over-Indexing

Excessive indexing can slow down writes without sufficiently aiding reads. If possible_keys lists indexes that are not being used, it’s possible that you can remove these using DROP INDEX. But just because an index isn’t being used for a specific query doesn’t mean it’s not supporting other queries, joins, or even constraints. Before dropping an index, analyze its usage across the workload.

Tools to Complement EXPLAIN

EXPLAIN is a great starting point for figuring out why your MySQL queries are struggling to meet your expectations, but it’s not the only tool you have at your disposal. Ideally, you should automate query analysis as much as possible. Pairing EXPLAIN with other tools can make the job easier and faster, especially when you’re dealing with lots of queries or need a clearer view of what’s going on:

  • Percona Toolkit: Provides advanced query profiling tools. For example, pt-query-digest examines your query logs to show which ones run slower or consume the most resources.

  • EverSQL: Web-based tool that offers slow query log analysis, so you can skip sorting through EXPLAIN outputs and quickly get a list of inefficient queries. You can then plug in these queries, one at a time, for optimization.
  • Releem: Automates the entire query analysis and optimization process. Lists your top 100 queries (by count, total load time, and average execution time) in an easy-to-read dashboard. Also offers query optimization recommendations delivered right to your inbox when an inefficient query is identified. Once you’ve made the recommended changes, you’ll receive follow-up emails detailing the performance improvements.

Add EXPLAIN to your Toolbox Today

EXPLAIN is a great tool to add to your MySQL toolkit. It lays out how your queries run, giving you clear insights to manage your database better and build better queries. You can pinpoint issues like full scans, missing indexes, and inefficient joins. Ready to get started? Begin by running EXPLAIN on queries found in your slow query log. This will help you familiarize yourself with the output table and learn what to look for.

EXPLAIN describes the execution plan; it does not diagnose index fragmentation. Do not rebuild an index solely because a plan looks inefficient.

Article by

  • Roman Agabekov
    Founder & CEO
    Roman Agabekov has 17 years of experience managing and optimizing MySQL and MariaDB in high-load environments. He founded Releem to automate routine database management tasks like performance monitoring, tuning, and query optimization. His articles share practical insights to help others maintain and improve their databases.
Keeping an Eye on MySQL with Health Checks
MySQL Performance Tuning Guide
How MySQL Configuration Impacts the Performance of Web Applications
How do you keep high-performance MySQL configuration?

Explore More
Get your own database advisor
Try Releem premium today for FREE!
Start Free Trial