Decoding MySQL EXPLAIN Plans: A Developer's Guide to Indexing

You've got a slow query. You've heard you should "add an index." But when you run EXPLAIN on your query, you're greeted with a wall of columns—type, possible_keys, key, rows, Extra—and you're not sure what any of it means.

This guide will teach you to read EXPLAIN plans like a pro. By the end, you'll know exactly why a query is slow and what index to create to fix it.

The Basics: Running EXPLAIN

To analyze any SELECT query, prefix it with EXPLAIN:

EXPLAIN SELECT * FROM orders WHERE customer_id = 123 AND status = 'pending';

MySQL will return a row (or multiple rows for JOINs) describing how it plans to execute the query. Let's break down the important columns.

The Most Important Columns

1. type — The Access Method

This tells you how MySQL is finding rows. From best to worst:

If you see ALL, you almost certainly need an index.

2. possible_keys vs key

• possible_keys: Indexes MySQL could use for this query
• key: The index MySQL actually chose to use

If possible_keys shows indexes but key is NULL, MySQL decided a full table scan was faster. This often happens with small tables or poorly selective indexes.

If possible_keys is NULL, there's no relevant index at all. Time to create one.

3. rows — Estimated Rows Examined

This is MySQL's estimate of how many rows it needs to scan. Lower is better. If you're querying 10 rows but rows shows 1,000,000, your query is examining far more data than necessary.

4. Extra — Additional Information

Watch for these red flags:

• Using filesort: MySQL needs to sort results in memory or on disk. Often caused by ORDER BY on non-indexed columns.
• Using temporary: MySQL created a temporary table. Common with GROUP BY or DISTINCT on non-indexed columns.
• Using where: MySQL is filtering after fetching rows. Not necessarily bad, but combined with high rows count, it's a problem.

Good signs in Extra:

• Using index: A covering index is being used—MySQL can answer the query from the index alone without reading the table.

Real-World Example: Optimizing a Slow Query

Let's say you have an e-commerce database with an orders table:

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    status VARCHAR(20) NOT NULL,
    created_at DATETIME NOT NULL,
    total DECIMAL(10,2) NOT NULL
);

And this slow query:

SELECT * FROM orders 
WHERE customer_id = 123 
AND status = 'pending'
ORDER BY created_at DESC;

Run EXPLAIN:

+----+-------------+--------+------+---------------+------+---------+------+--------+-----------------------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows   | Extra                       |
+----+-------------+--------+------+---------------+------+---------+------+--------+-----------------------------+
|  1 | SIMPLE      | orders | ALL  | NULL          | NULL | NULL    | NULL | 524288 | Using where; Using filesort |
+----+-------------+--------+------+---------------+------+---------+------+--------+-----------------------------+

The diagnosis:

• type: ALL — Full table scan. Bad.
• possible_keys: NULL — No usable indexes. Very bad.
• rows: 524288 — Scanning half a million rows to find a few. Terrible.
• Using filesort — Sorting 524k rows to apply ORDER BY. Slow.

The Fix: Create a Composite Index

This query filters by customer_id and status, then orders by created_at. The ideal index covers all three:

CREATE INDEX idx_orders_customer_status_created 
ON orders (customer_id, status, created_at);

Run EXPLAIN again:

+----+-------------+--------+------+------------------------------------+------------------------------------+---------+-------------+------+-------------+
| id | select_type | table  | type | possible_keys                      | key                                | key_len | ref         | rows | Extra       |
+----+-------------+--------+------+------------------------------------+------------------------------------+---------+-------------+------+-------------+
|  1 | SIMPLE      | orders | ref  | idx_orders_customer_status_created | idx_orders_customer_status_created | 86      | const,const |   12 | Using where |
+----+-------------+--------+------+------------------------------------+------------------------------------+---------+-------------+------+-------------+

The improvement:

• type: ref — Using the index efficiently.
• key — Our new index is being used.
• rows: 12 — Only scanning 12 rows instead of 524k. 43,690x improvement!
• Using filesort is gone — The index handles the ordering.

Common Indexing Patterns

Pattern 1: Equality + Range

If your query uses = on some columns and </>/BETWEEN on others, put the equality columns first in the index.

-- Query
WHERE status = 'active' AND created_at > '2025-01-01'

-- Index
CREATE INDEX idx_status_created ON orders (status, created_at);

Pattern 2: Covering Indexes

If you only need specific columns, include them in the index to avoid table lookups:

-- Query
SELECT customer_id, total FROM orders WHERE status = 'pending';

-- Index (covers all needed columns)
CREATE INDEX idx_status_covering ON orders (status, customer_id, total);

Pattern 3: Avoid Function Calls on Indexed Columns

This index won't be used:

WHERE YEAR(created_at) = 2025  -- Can't use index on created_at

Rewrite as:

WHERE created_at >= '2025-01-01' AND created_at < '2026-01-01'  -- Uses index

Let CLI Pal Do the Work

Reading EXPLAIN plans is a skill worth having, but it takes time. CLI Pal's MySQL Optimizer does this automatically:

1. It detects slow queries from performance_schema
2. Runs EXPLAIN on each one
3. Analyzes the results with AI
4. Generates specific CREATE INDEX statements with explanations

You get the exact index you need, with an explanation of why it helps. No manual EXPLAIN required.

But when you do need to dig deeper, you now have the knowledge to read EXPLAIN plans yourself and understand exactly what's happening.

Try CLI Pal's MySQL Optimizer and see AI-powered indexing recommendations for your queries.