How to Read an SQL EXPLAIN Plan — A Practical Guide

1. What an EXPLAIN Plan Actually Shows

When you run a query, the optimizer picks a strategy before touching any data: which index to use, which table to read first in a JOIN, whether to sort with an index or in memory. EXPLAIN shows you that strategy without running the query. Prepend it to any SELECT, INSERT, UPDATE, or DELETE:

EXPLAIN SELECT id, order_date, total
FROM orders
WHERE customer_id = 1042
ORDER BY order_date DESC;

You get one row of output per table access in the query. A three-table JOIN produces at least three rows. Each row describes how the engine plans to fetch rows from one table, and the rows together tell you the join order.

Two variants are worth knowing:

• EXPLAIN ANALYZE (MySQL 8.0.18+, PostgreSQL) — actually executes the query and reports actual time and row counts, not just estimates. This is what you use when the estimate-based plan looks fine but the query is still slow.
• EXPLAIN FORMAT=JSON (MySQL) / EXPLAIN (FORMAT JSON) (PostgreSQL) — verbose output that includes cost estimates. Useful when you need to compare two candidate plans precisely.

2. The Columns: id, select_type, table, type, key, rows, Extra

Here is a typical MySQL EXPLAIN output for a two-table join, slightly trimmed to fit:

+----+-------------+-------+------+------------------+---------+---------+-----------------+--------+-----------------------+
| id | select_type | table | type | possible_keys    | key     | key_len | ref             | rows   | Extra                 |
+----+-------------+-------+------+------------------+---------+---------+-----------------+--------+-----------------------+
|  1 | SIMPLE      | c     | const| PRIMARY,idx_email| PRIMARY | 4       | const           |      1 | Using index           |
|  1 | SIMPLE      | o     | ref  | idx_customer     | idx_customer | 4  | shopdb.c.id     |    214 | Using where; Using index |
+----+-------------+-------+------+------------------+---------+---------+-----------------+--------+-----------------------+

The columns you actually need to read, in the order that matters:

Two supporting columns deserve a mention: possible_keys (indexes the optimizer could have used — if it's NULL, no index on your WHERE columns exists at all) and key_len (how many bytes of the index are used — a shorter key_len than the full composite index means only a prefix of the index matched, classic evidence of the leftmost-prefix rule at work).

3. The type Column, Ranked Best to Worst

The type column describes how rows are located. Memorize this ranking — it is the fastest way to read EXPLAIN output at a glance:

Two practical readings of this table:

• For lookups and joins, you want const, eq_ref, or ref. Anything below range on a table with more than a few thousand rows deserves a question: why didn't the optimizer find an index?
• index vs ALL: a full index scan still touches every entry, but an index is much smaller than the table (especially a covering index), so it's cheaper. It commonly appears for ORDER BY satisfied by index order, or for COUNT(*) on InnoDB. Don't confuse "Using index" in Extra (good: index-only) with type=index (meh: whole index scanned).

One honest caveat: type=ALL on a 500-row lookup table is fine — reading 500 sequential rows beats an index dive. The ranking tells you what to investigate, not what to panic about. Table size and selectivity decide.

4. Red Flags: ALL, Using filesort, Using temporary

Red flag #1 — type = ALL on a big table

A full table scan means every row is read into the buffer pool to test your WHERE condition. If rows is in the hundreds of thousands and possible_keys is NULL, you're missing an index entirely. If possible_keys lists an index but key is NULL, the optimizer rejected it — common causes:

• The predicate matches too many rows (low selectivity), so a scan is genuinely cheaper.
• A function or expression wraps the column: WHERE DATE(created_at) = '2026-09-11' defeats the index; WHERE created_at >= '2026-09-11' AND created_at < '2026-09-12' uses it.
• Implicit type conversion: WHERE phone = 13800000000 against a VARCHAR column forces a scan.
• Stale statistics — run ANALYZE TABLE orders; and re-check.

Red flag #2 — Using filesort

Using filesort in Extra means the ORDER BY couldn't be served by index order, so MySQL sorts the matched rows itself. Sorting 20 rows is nothing; sorting 200,000 rows before throwing away all but 10 is the classic slow pagination query. The fix is a composite index that matches WHERE first, then ORDER BY:

-- Query: filter by customer, sort by date, take latest 10
SELECT id, order_date, total
FROM orders
WHERE customer_id = 1042
ORDER BY order_date DESC
LIMIT 10;

-- Index that removes both the scan AND the filesort:
ALTER TABLE orders ADD INDEX idx_cust_date (customer_id, order_date);

With (customer_id, order_date), MySQL jumps to customer 1042's entries via the index, walks them backwards (the index is already sorted by date within that customer), and stops after 10 rows. Extra should then show Using where with no filesort.

Red flag #3 — Using temporary

Using temporary means MySQL builds an internal temp table — typical for GROUP BY on an unindexed column, DISTINCT over a join, or UNION. Temp tables start in memory but spill to disk past tmp_table_size/max_heap_table_size, and disk temp tables are brutally slow. Mitigations:

• Make the GROUP BY column(s) an index prefix so grouping uses index order instead of a temp table.
• Reduce the input: filter rows earlier, avoid SELECT * so the temp rows stay narrow.
• Accept it for small aggregates — a temp table over 300 rows costs nothing.

Also watch for DEPENDENT SUBQUERY in select_type: the subquery re-executes for every outer row. A 10,000-row outer table turns a cheap subquery into 10,000 executions. Rewrite as a JOIN or a materialized derived table.

5. Case Study: Fixing a Slow Query with One Index

Here's a real shape of problem: an e-commerce order search that takes 4.2 seconds on a 1.2-million-row table. The schema (trimmed):

CREATE TABLE orders (
  id           BIGINT PRIMARY KEY AUTO_INCREMENT,
  customer_id  INT NOT NULL,
  status       VARCHAR(20) NOT NULL,
  order_date   DATETIME NOT NULL,
  total        DECIMAL(10,2) NOT NULL,
  KEY idx_status (status)
) ENGINE=InnoDB;

-- The slow query:
SELECT id, order_date, total
FROM orders
WHERE customer_id = 1042 AND status = 'shipped'
ORDER BY order_date DESC
LIMIT 10;

Before — the EXPLAIN plan

EXPLAIN SELECT id, order_date, total
FROM orders
WHERE customer_id = 1042 AND status = 'shipped'
ORDER BY order_date DESC LIMIT 10;

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

Reading this plan like a checklist:

• type = ALL with rows ≈ 1.2M: full table scan. The engine reads every row.
• possible_keys = idx_status but key = NULL: the only candidate index was on status, and since 'shipped' matches ~40% of the table, the optimizer correctly decided the index wasn't worth it.
• No index on customer_id at all — the most selective column in the WHERE clause.
• Using filesort: after filtering, it sorts all matching rows in memory just to keep the newest 10.

The fix — one composite index

ALTER TABLE orders
  ADD INDEX idx_cust_status_date (customer_id, status, order_date);

Column order matters: equality predicates first (customer_id, status), then the range/sort column (order_date). This single index serves the filter and the ORDER BY.

After — the EXPLAIN plan

+----+-------------+--------+-------+----------------------+----------------------+---------+------+------+--------------------------------------------+
| id | select_type | table  | type  | possible_keys        | key                  | key_len | ref  | rows | Extra                                      |
+----+-------------+--------+-------+----------------------+----------------------+---------+------+------+--------------------------------------------+
|  1 | SIMPLE      | orders | ref   | idx_cust_status_date | idx_cust_status_date | 86      | const,const | 19 | Using where; Using index               |
+----+-------------+--------+-------+----------------------+----------------------+---------+------+------+--------------------------------------------+

What changed:

Because the query only selects id, order_date, and total — and id is implicitly in every InnoDB secondary index — extending the index to (customer_id, status, order_date, total) would make it fully covering and print Using index without touching the clustered index at all. In this plan, Using index already appears because the needed columns fit the index plus primary key. 1.2M rows examined dropped to 19: that's what reading EXPLAIN output is for.

6. EXPLAIN in PostgreSQL and SQL Server

The MySQL columns above are InnoDB-specific, but the concepts port directly.

PostgreSQL

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, order_date, total
FROM orders
WHERE customer_id = 1042 AND status = 'shipped'
ORDER BY order_date DESC
LIMIT 10;

PostgreSQL prints a tree, not a table. Key node types to recognize: Seq Scan (= MySQL's ALL), Index Scan, Index Only Scan (= covering), Bitmap Heap Scan (index used as a row filter — good for medium selectivity), Sort with a large row count (= filesort), and Hash Join / Nested Loop. With ANALYZE, compare estimated rows vs actual rows at each node — a 100× mismatch is where plans go wrong, usually fixed with better statistics targets or a rewritten predicate.

SQL Server

SET STATISTICS XML ON;
SELECT id, order_date, total
FROM orders
WHERE customer_id = 1042 AND status = 'shipped'
ORDER BY order_date DESC;
SET STATISTICS XML OFF;

The XML plan (or the graphical plan in SSMS) flags Table Scan / Clustered Index Scan (= ALL), Sort operators (= filesort), and Hash Match with spools (= temporary). SSMS even labels "missing index" suggestions directly on the plan — treat them as candidates, not commands; verify each with the same before/after method as above.

7. A Practical Reading Workflow

When a query is slow, work through the plan in this order:

1. Scan the type column first. Any ALL or index on a large table? That's your suspect.
2. Check key against possible_keys. NULL with candidates listed → optimizer rejected the index (selectivity, functions on columns, type mismatch). NULL with no candidates → create the index.
3. Look at rows. Estimates near table size confirm a scan; huge products across join rows confirm a nested-loop blowup.
4. Read Extra last, but seriously. Using filesort and Using temporary on large inputs are where the remaining milliseconds hide after indexing.
5. Verify with EXPLAIN ANALYZE. Estimates can lie; actuals can't. If actual rows deviate 10× from estimates, refresh statistics before tuning further.
6. Change one thing at a time — add the index, re-run EXPLAIN, confirm type improved and rows dropped. Then move to the next table in the plan.

Reading plans is a habit, not a fire drill. Run EXPLAIN on every query that touches a table with more than ~100k rows before it ships, not after it pages you at 3 a.m. And when you paste the offending monster query into EXPLAIN, paste it through a SQL formatter first — a formatted query makes the plan's table order and predicates far easier to line up.

FAQ — Reading SQL EXPLAIN Plans