How to Fix Slow MySQL Queries Using EXPLAIN Analysis

You're debugging a slow query that's been running for 45 seconds. Your first instinct is to add an index, but you don't actually know what MySQL is doing under the hood. You're flying blind. This is where most developers go wrong—they optimize without understanding.

The EXPLAIN statement is your window into MySQL's decision-making process. It shows you exactly how the database plans to retrieve your data, from which indexes it uses to which tables it joins first. Without mastering EXPLAIN, you're essentially guessing at optimizations. With it, you have surgical precision.

This guide goes beyond the basics. You'll learn every column in the EXPLAIN output, understand join types that most developers skip over, and decode the cryptic "Extra" information that actually reveals performance killers. By the end, you'll be able to look at any execution plan and immediately spot bottlenecks.

Table of Contents

1. What EXPLAIN Actually Does
2. The Complete EXPLAIN Output: Column by Column
3. Understanding Join Types
4. The Extra Column: The Performance Goldmine
5. Index Usage and Key Selection
6. Practical Examples: Real-World Query Plans
7. Common Mistakes and Pitfalls
8. EXPLAIN FORMAT Options
9. When NOT to Rely on EXPLAIN
10. Quick Reference Checklist

What EXPLAIN Actually Does

When you run EXPLAIN followed by a SELECT query, MySQL doesn't actually execute the query. Instead, it parses the SQL, optimizes it, and tells you the plan it would use. This is crucial: EXPLAIN is a prediction, not a guarantee of what will happen at runtime.

MySQL's query optimizer is cost-based. It estimates the "cost" of different execution plans by considering factors like:

• How many rows need to be read from each table
• Whether indexes are available and how selective they are
• The order in which tables should be joined
• Whether sorting is needed and if it can be done with an index

The optimizer chooses the plan with the lowest estimated cost. This is why understanding EXPLAIN helps you guide the optimizer toward better decisions—by adding indexes, restructuring queries, or providing hints.

Important: The estimates aren't always perfect, especially with complex queries or skewed data. But EXPLAIN gives you 90% of the information you need to optimize effectively.

The Complete EXPLAIN Output: Column by Column

When you run EXPLAIN SELECT ..., MySQL returns a table with 12 columns in MySQL 5.7+ (and 11 in older versions). Let's break down each one:

1. id

The SELECT identifier—the number assigned to each SELECT in your query.

In a simple SELECT, this is always 1. But in a subquery or UNION, you might see:

EXPLAIN SELECT * FROM users WHERE id IN (SELECT user_id FROM orders);

Output:

id | select_type | table
1  | PRIMARY     | users
2  | SUBQUERY    | orders

The subquery gets a higher id number. If you see the same id multiple times (in UNION queries), they're executed sequentially.

What to look for: Subqueries with higher ids are executed first, which can be inefficient. You might restructure as a JOIN instead.

2. select_type

Tells you what type of SELECT this is. The most common values:

• SIMPLE: A straightforward SELECT with no subqueries or UNIONs
• PRIMARY: The outermost SELECT in a query with subqueries
• SUBQUERY: A subquery inside the WHERE or FROM clause
• DERIVED: A subquery in the FROM clause (also called a derived table)
• UNION: The second or later SELECT in a UNION
• UNION RESULT: The result of combining UNION queries
• DEPENDENT SUBQUERY: A subquery that references columns from the outer query (executed once per row—slow!)

What to look for: DEPENDENT SUBQUERY is a red flag. These run once for every row in the outer query, creating an N+1 scenario. Rewrite as a JOIN when possible.

-- SLOW: Dependent subquery
SELECT * FROM users u WHERE id IN (
  SELECT user_id FROM orders WHERE user_id = u.id
);

-- BETTER: Join
SELECT DISTINCT u.* FROM users u
INNER JOIN orders o ON u.id = o.user_id;

3. table

The table being accessed. In a JOIN, you see one row per table.

For derived tables (subqueries in FROM), you see something like <subquery2>, referring back to the id number.

What to look for: Make sure you're querying the table you think you are. In complex queries, it's easy to accidentally reference the wrong table.

4. type (aka: join type)

This is one of the most important columns. It tells you how MySQL accesses the table—from fastest to slowest:

• system: The table has only one row (rare, basically const)
• const: The table has one matching row (exact match on PRIMARY KEY or UNIQUE key)
• eq_ref: One row from a table is read for each row in the preceding table
• ref: Rows with matching index values
• fulltext: A FULLTEXT index is used
• ref_or_null: Like ref, but includes NULL values
• index_merge: Multiple indexes are combined
• unique_subquery: A subquery returns a unique value
• index_subquery: Like unique_subquery but returns multiple values
• range: Index is used to find rows in a given range
• index: The entire index is scanned
• ALL: Full table scan

The hierarchy of speed (best to worst):

const > eq_ref > ref > range > index > ALL

What to look for: You want types on the left side of this spectrum. If you see ALL, you're missing an index.

5. possible_keys

The indexes MySQL could have used for this query. This is just a list of candidates—not what it actually chose.

What to look for: If this is NULL, MySQL found no suitable indexes. That usually means you need to add one.

6. key

The index MySQL actually chose to use. NULL means no index was used.

What to look for: Does it match what you expected? If not, the optimizer may believe a full scan is cheaper.

7. key_len

The length (in bytes) of the index key that MySQL uses.

-- If your composite index (user_id INT, order_date DATE) is used:
-- user_id: 4 bytes
-- order_date: 3 bytes
-- key_len: 7

What to look for: A shorter key_len on a composite index often means only the first column is being used.

8. ref

Shows which column(s) are compared against the index to retrieve rows.

EXPLAIN SELECT * FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'completed';

What to look for: If you see func, a function is being applied and the index may not be used efficiently.

9. rows

MySQL's estimate of how many rows will be examined to produce the result.

What to look for: A very large number usually signals an inefficient plan or missing index.

10. filtered

A percentage showing how many rows will be filtered by the WHERE clause.

What to look for: High filtered values early in a JOIN mean too many rows are being read.

11. Extra

This column contains additional information that doesn't fit elsewhere and often reveals the biggest performance issues.

Understanding Join Types

Let's dive deeper into the type column because this is where you'll find most performance problems.

const: The Best Case

A constant lookup using a PRIMARY KEY or UNIQUE index.

EXPLAIN SELECT * FROM users WHERE id = 5;

This retrieves exactly one row. Microseconds. You want as many of these as possible in your execution plan.

eq_ref: One Row Per Row (Good for Joins)

Used in JOINs where each row from one table matches exactly one row in another table via a PRIMARY KEY or UNIQUE index.

EXPLAIN SELECT u.*, o.* FROM users u
INNER JOIN orders o ON u.id = o.user_id;

If o.user_id references u.id via a FOREIGN KEY or unique constraint, you'll see eq_ref for the orders table. This means for each user row, MySQL finds exactly one matching order row using the index.

ref: Many Rows Per Value (Still Good)

The index is used, but multiple rows might match.

EXPLAIN SELECT * FROM orders WHERE status = 'pending';

If you have an index on status, MySQL uses it to quickly find all rows with status = 'pending'. But multiple rows match, so it's not as fast as const or eq_ref.

range: Index with a Range Condition

The index is used to find rows within a range.

EXPLAIN SELECT * FROM orders 
WHERE created_at >= '2024-01-01' AND created_at <= '2024-12-31';

If an index exists on created_at, MySQL uses it to find the range. This is efficient for date ranges, numeric ranges, etc.

Range conditions that use indexes:

• WHERE id > 10
• WHERE id BETWEEN 1 AND 100
• WHERE status IN ('pending', 'processing')
• WHERE name LIKE 'A%' (starts with A)

Range conditions that DON'T use indexes:

• WHERE name LIKE '%A' (ends with A)
• WHERE YEAR(date_col) = 2024 (function applied)

index: Full Index Scan (Watch Out)

The entire index is scanned, not just a portion. This is slower than const, eq_ref, ref, or range, but faster than a full table scan.

EXPLAIN SELECT COUNT(*) FROM orders;

If you have an index on the orders table, MySQL might scan the entire index to count rows (faster than reading the full table).

When you see this: Evaluate if you can add a WHERE clause to narrow results, or if a covering index would help.

ALL: Full Table Scan (Optimize This)

Every single row in the table is read. This is the red flag.

EXPLAIN SELECT * FROM users WHERE email LIKE '%gmail%';

With LIKE '%value%', no index helps. Every row must be checked.

What to do:

• Add an index if possible
• Restructure the query to avoid the condition
• Use FULLTEXT search for complex text matching
• Consider denormalization or a search engine like Elasticsearch

The Extra Column: The Performance Goldmine

The Extra column contains nuanced information that reveals performance implications. Here are the most important values:

Using index

The query can be satisfied entirely by the index—MySQL doesn't need to read the actual table data.

-- Assuming index on (user_id, created_at)
EXPLAIN SELECT user_id, created_at FROM orders WHERE user_id = 5;

This is called a covering index. It's faster because reading from the index (stored sequentially in memory) is faster than jumping to the table for each row.

What to look for: If you have frequently-run queries that could use covering indexes, add them. The performance difference is noticeable.

Using where

MySQL applies a WHERE clause after retrieving rows from the table. This is normal but worth noting when combined with other information.

If you see Using where AND type: ALL, it means a full table scan happens first, then rows are filtered. That's bad. You want the index to handle the filtering.

Using temporary

MySQL creates a temporary table to process the result set. This happens with DISTINCT, GROUP BY, or UNION queries.

EXPLAIN SELECT DISTINCT user_id FROM orders;

Temporary tables are slow on large datasets. They're created in memory if small enough, or on disk if large.

What to look for: If you see this with a large table, it's a problem. Try rewriting the query or using a covering index.

Using filesort

Rows are sorted using a temporary file on disk (or in memory, despite the name). This happens when ORDER BY can't be satisfied by an index.

EXPLAIN SELECT * FROM users ORDER BY created_at;

If no index on created_at, MySQL reads all rows and sorts them—slow on large tables.

What to optimize:

• Add an index on the ORDER BY column
• If ORDER BY is on a joined table, ensure the index matches your query
• For compound sorts, index order matters: if you do ORDER BY a, b, use index on (a, b)

Using index condition

MySQL uses index condition pushdown (ICP). It filters rows at the index level before reading table data. This is an optimization that's usually automatic in MySQL 5.6+.

EXPLAIN SELECT * FROM orders WHERE user_id = 5 AND created_at > '2024-01-01';

If you have an index on (user_id, created_at), both conditions are evaluated at the index level, reducing table reads.

Using join buffer

When joining tables, if the second table has no suitable index, MySQL buffers rows from the first table and scans the second table repeatedly. This is a sign you might be missing an index on the join column.

EXPLAIN SELECT u.*, o.* FROM users u
JOIN orders o ON u.id = o.user_id;

If o.user_id has no index, you'll see Using join buffer.

What to optimize: Add an index on the join column.

Distinct

MySQL stops searching for more matching rows once it finds the first one. Used with LIMIT and UNIQUE constraints.

Impossible WHERE

The WHERE clause is always false.

WHERE id = 1 AND id = 2

MySQL will return an empty result without touching the table.

Index Usage and Key Selection

One of the most confusing scenarios is when you add an index but EXPLAIN shows it's not being used. Here's why:

The Optimizer Chooses Not to Use It

Sometimes MySQL estimates that a full table scan is faster than using an index. This often happens with:

• Very small tables (100 rows). Scanning the whole table might be faster than index lookup.
• Highly selective WHERE conditions where the optimizer thinks many rows will be returned.
• Poor column cardinality (few unique values). If a column has only 3 unique values across a million rows, an index isn't helpful.

Index Column Order Matters

For composite indexes, the column order is critical.

-- Index created as: (user_id, status)
EXPLAIN SELECT * FROM orders WHERE user_id = 5 AND status = 'pending';

Both columns are used efficiently.

-- Same index: (user_id, status)
EXPLAIN SELECT * FROM orders WHERE status = 'pending';

The index can't be used because you're filtering on the second column without the first. MySQL can't skip to status = 'pending' without first knowing the user_id.

Rule: Index columns in order from most to least selective, then by frequency of use.

Updating Statistics

If your index exists but EXPLAIN shows NULL in the key column, run:

ANALYZE TABLE table_name;

This updates statistics that the optimizer uses. Sometimes stale statistics cause bad decisions.

Practical Examples: Real-World Query Plans

Let's work through realistic scenarios and what to do with the EXPLAIN output.

Example 1: The Missing Index

-- Setup
CREATE TABLE users (
  id INT PRIMARY KEY,
  email VARCHAR(255),
  created_at DATETIME
);

EXPLAIN SELECT * FROM users WHERE email = 'john@example.com';

Output:

id | select_type | table | type | possible_keys | key  | rows   | Extra
1  | SIMPLE      | users | ALL  | NULL          | NULL | 100000 | Using where

All red flags: type is ALL (full scan), no index used.

Fix:

CREATE INDEX idx_email ON users(email);

-- Now rerun EXPLAIN:
-- type changes to: const or ref
-- key shows: idx_email

Example 2: The Slow Join

-- Setup
CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(255)
);

CREATE TABLE orders (
  id INT PRIMARY KEY,
  user_id INT,
  amount DECIMAL(10, 2)
);

EXPLAIN SELECT u.name, SUM(o.amount) FROM users u
JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;

Output (before optimization):

id | table  | type | key  | rows   | Extra
1  | users  | ALL  | NULL | 1000   | Using temporary; Using filesort
2  | orders | ALL  | NULL | 100000 | Using join buffer

This query is a disaster:

• Full scan of users (1000 rows)
• For each user, full scan of orders (100,000 rows)
• Join buffer being used (no index on join column)
• Temporary table for GROUP BY
• Filesort for sorting

Fix:

-- Add indexes
CREATE INDEX idx_user_id ON orders(user_id);

-- Now:
-- orders join type becomes eq_ref (much faster)
-- join buffer is eliminated
-- query is potentially 100x faster

Example 3: The Covering Index

EXPLAIN SELECT user_id, created_at FROM orders 
WHERE user_id = 5 ORDER BY created_at DESC LIMIT 10;

Before (without covering index):

type | key      | Extra
ref  | idx_user | Using where; Using filesort

After (with covering index):

CREATE INDEX idx_user_date ON orders(user_id, created_at DESC);

EXPLAIN SELECT user_id, created_at FROM orders 
WHERE user_id = 5 ORDER BY created_at DESC LIMIT 10;

Output:

type | key           | Extra
ref  | idx_user_date | Using index

Much faster: Using index means no table reads needed. Everything comes from the index.

Common Mistakes and Pitfalls

Mistake 1: Applying Functions to Indexed Columns

EXPLAIN SELECT * FROM users WHERE YEAR(created_at) = 2024;

Even if created_at is indexed, the YEAR() function prevents index use.

type | key  | Extra
ALL  | NULL | Using where

Fix:

SELECT * FROM users 
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';

Now the index is used.

Mistake 2: OR Conditions Without Proper Indexes

EXPLAIN SELECT * FROM orders 
WHERE status = 'pending' OR status = 'processing';

If you have an index on status but use OR, MySQL might still do a full scan on some versions.

Fix:

SELECT * FROM orders WHERE status IN ('pending', 'processing');

Or ensure your index supports both conditions.

Mistake 3: LIMIT Without ORDER BY Understanding

EXPLAIN SELECT * FROM orders 
WHERE user_id = 5 
ORDER BY created_at DESC 
LIMIT 10;

If there's an index on (user_id, created_at), MySQL can retrieve the 10 most recent orders efficiently. But if not:

Extra: Using filesort

All matching rows are retrieved, sorted, then limited. On 100,000 matching rows, this is slow.

Mistake 4: Trusting the Row Estimate Too Much

The rows column is an estimate. On tables with skewed data or poor statistics:

ANALYZE TABLE my_table;

Then recheck EXPLAIN.

EXPLAIN FORMAT Options

MySQL gives you different ways to view execution plans:

EXPLAIN FORMAT=JSON

Provides detailed JSON output with more information than the table format.

EXPLAIN FORMAT=JSON SELECT * FROM users WHERE id = 5\G

This shows:

• Exact costs calculated
• Query optimization info
• Index details
• Timing estimates (if available)

Use this when the standard format doesn't give you enough detail.

EXPLAIN FORMAT=TREE

MySQL 8.0.16+. Shows the execution plan as an indented tree.

EXPLAIN FORMAT=TREE SELECT * FROM users WHERE id = 5;

Output:

-> Const row (cost=0.00 rows=1)
  -> Table scan on users
      Const condition: id = 5

Great for understanding the logical flow of complex queries.

ANALYZE Statement

MySQL 8.0+. Actually executes the query and shows real statistics (not estimates).

ANALYZE SELECT * FROM users WHERE id IN (1,2,3);

Shows what actually happened vs. what was predicted. Invaluable for debugging why EXPLAIN's estimate was wrong.

When NOT to Rely on EXPLAIN

EXPLAIN is powerful but has limitations:

EXPLAIN Doesn't Account for Cache

If your data is already in the buffer pool (MySQL's in-memory cache), queries run much faster. EXPLAIN doesn't know if data is cached.

A query that looks slow in EXPLAIN might run fast if the data is frequently accessed.

EXPLAIN Estimates Can Be Very Wrong

With complex queries involving multiple JOINs, subqueries, or aggregations, the row estimates are often inaccurate.

Always verify with real timing:

SET PROFILING=1;
SELECT * FROM your_big_query;
SHOW PROFILES;

Or use ANALYZE (mentioned above).

EXPLAIN Doesn't Show Concurrency Issues

If 100 queries are running simultaneously, EXPLAIN on a single query won't show contention or locking delays.

For production issues, check slow logs and enable general query log during investigation. See our guide on enabling and analyzing slow query logs for performance optimization.

EXPLAIN Doesn't Account for Replication Lag

In a master-slave setup, queries on replicas might be slower than EXPLAIN suggests due to replication lag.

For more on replication, see our master-slave replication setup guide.

Quick Reference Checklist

When analyzing a slow query:

• Check select_type: Is it DEPENDENT SUBQUERY? Rewrite as JOIN.
• Check type: Aim for const, eq_ref, or ref. ALL is bad.
• Check possible_keys: Is an appropriate index available? If NULL, add one.
• Check key: Is MySQL using the index you expected?
• Check rows: Does the estimate match reality? Run ANALYZE TABLE if off.
• Check Extra:
  • "Using index" = Good (covering index)
  • "Using filesort" = Potential problem on large result sets
  • "Using temporary" = Slow for large tables
  • "Using join buffer" = Missing index on join column
• Check key_len: On composite indexes, is it shorter than expected? Wrong column order?
• Avoid common mistakes:
  • Functions on indexed columns
  • OR conditions without proper indexes
  • LIMIT without understanding ORDER BY index needs
• Verify with real data: Use ANALYZE or PROFILING on actual queries.

Conclusion

EXPLAIN is the most powerful tool in a MySQL developer's toolkit. Most performance problems hide in the execution plan—bad join orders, missing indexes, or inefficient WHERE clauses. Learning to read EXPLAIN output with precision separates okay developers from great ones.

The key insight: EXPLAIN tells you what MySQL will do, and the columns tell you why. Once you understand the why, optimization becomes logical instead of guesswork.

Start with your slowest queries. Run EXPLAIN. Look for ALL scans, Using filesort, Using temporary, and join buffer warnings. Add indexes strategically. Recheck EXPLAIN. Watch the performance improve.

If you've set up MySQL and want to identify slow queries in your production environment, our guide on enabling and analyzing slow query logs for performance optimization walks you through capturing and analyzing real-world slow queries. And if you're running a multi-server setup, understanding EXPLAIN becomes even more critical for optimizing master-slave replication queries.

Need expert help? We offer comprehensive database services across MySQL, PostgreSQL, MariaDB, and more — including troubleshooting, optimization, backups, and full database management. Reach out for a consultation and let’s discuss how we can support your database needs.