How to use EXPLAIN to fix slow queries in MySQL
APR 11, 2025 • WRITTEN BY ROMAN AGABEKOV
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.
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.
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)?
Look to see how MySQL is retrieving the rows. Ideally, you want to see either ref, eq_ref, or const, which indicates effective indexing. If you see ALL or index instead this means that either an index or a table is being scanned entirely.
The goal is to push toward index-driven access methods wherever possible.
The goal is to push toward index-driven access methods wherever possible.
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)
Look at the row count for each step in the query. Less is always better. Check filtered to find out what percentage of those rows actually match your WHERE clause.
If you’re seeing big rows numbers with a tiny filtered percentage, it’s usually a sign that you’re missing an index or your conditions are too broad. For example, if rows says 1,000 but filtered is 5.00, MySQL sifts through 1,000 rows just to keep 50.
If you’re seeing big rows numbers with a tiny filtered percentage, it’s usually a sign that you’re missing an index or your conditions are too broad. For example, if rows says 1,000 but filtered is 5.00, MySQL sifts through 1,000 rows just to keep 50.
5. Is MySQL Doing Unnecessary Extra Work (Extra)?
Does Extra shows “Using index”, meaning the query pulls data from the index alone? Or do you see “Using filesort” or “Using temporary”, both of which slow things down?
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
Type = ALL means full table scan. MySQL is looking at every single row. This can cripple performance for large tables. It’s like searching a warehouse for a specific item but checking every box instead of using a catalog.
If a query is frequently scanning an entire inventory table (type ALL with 10,000 rows), that’s your cue to create an index. When you rerun EXPLAIN, type should change and rows should have dropped significantly. In effect, you’ve given MySQL a table of contents so it can jump to relevant rows instead of searching the entire table.
If a query is frequently scanning an entire inventory table (type ALL with 10,000 rows), that’s your cue to create an index. When you rerun EXPLAIN, type should change and rows should have dropped significantly. In effect, you’ve given MySQL a table of contents so it can jump to relevant rows instead of searching the entire table.
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';The issue lies in the LIKE condition. %yahoo.com starts with a wildcard (%), which tells MySQL to scan the email column for every row for a match ending in “yahoo.com”. This renders standard indexes useless.
To address this problem, rewrite the query to remove the leading wildcard. This will only work if a FULLTEXT index exists on the email: column:
- Trailing wildcards (LIKE ‘john%’) are index-friendly. MySQL can efficiently leverage an index on email to find rows that start with “john.
- Leading wildcards (LIKE ‘%yahoo.com’) aren’t index-friendly. The % at the beginning means MySQL can’t narrow down a starting point. It must scan everything to see if it ends with “yahoo.com”.
To address this problem, rewrite the query to remove the leading wildcard. This will only work if a FULLTEXT index exists on the email: column:
SELECT email FROM users WHERE MATCH(email) AGAINST('yahoo.com');3. Optimize Inefficient Joins
Joins are powerful but can exponentially increase query load when not indexed correctly. MySQL needs a fast way to match rows when joining two tables. Without indexes, it may scan the tables repeatedly, multiplying the workload.
For example, if type is listed as ALL for both tables being joined and there are 8,000 orders (rows) and 2,000 customers (rows). MySQL could process millions of combinations for this join.
You can address this by indexing the join column (CREATE INDEX idx_customer_id ON customers(id);). This may shift the customers table type to something more efficient like eq_ref. But keep in mind:
For example, if type is listed as ALL for both tables being joined and there are 8,000 orders (rows) and 2,000 customers (rows). MySQL could process millions of combinations for this join.
You can address this by indexing the join column (CREATE INDEX idx_customer_id ON customers(id);). This may shift the customers table type to something more efficient like eq_ref. But keep in mind:
- The column needs to be unique or primary in the referenced table.
- All parts of any composite index must be covered by the join condition.
- The optimizer must consider it the best 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:
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:
- MySQL Workbench: MySQL Workbench offers visual EXPLAIN outputs. It shows you the execution plan in a way that’s easier to visualize. You can also write queries (and add indexes) and then test how long they take to run.
- 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.
WRITTEN BY ROMAN AGABEKOV