EXPLAIN shows you the plan the database intends to execute. It is the only tool that answers the question every slow query eventually asks: "what is the engine actually doing, and is that what I assumed it would do?" A plan is not a score — it is a description, and reading it well is the difference between guessing and fixing.
How does EXPLAIN reveal a query's plan?
Every engine has a way to ask for the plan, and the spelling differs. SQLite uses EXPLAIN QUERY PLAN, MySQL uses EXPLAIN (and EXPLAIN FORMAT=JSON for the detailed form), PostgreSQL uses EXPLAIN with optional ANALYZE, and SQL Server uses SET STATISTICS IO, ON or the graphical execution plan. All of them answer the same question.
How do you read a plan?
Start with the two words that matter most: SCAN and SEARCH. SCAN means the engine reads every row of the table and evaluates the condition on each one. SEARCH means the engine uses an index to jump directly to the rows that match. Here is a three-table join with no secondary indexes at all:
EXPLAIN QUERY PLAN
SELECT u.name, m.title
FROM viewing_events v
JOIN users u ON u.user_id = v.user_id
JOIN movies m ON m.movie_id = v.movie_id
WHERE v.completed = 1;
QUERY PLAN
|--SCAN v
|--SEARCH u USING INTEGER PRIMARY KEY (rowid=?)
`--SEARCH m USING INTEGER PRIMARY KEY (rowid=?)
Read it from the inside out. The engine scans viewing_events, testing the completed flag on each of the twenty-four rows, and for each row that passes it looks up the user by primary key and the movie by primary key. Those two lookups are cheap because the primary keys are indexed automatically. The expensive step is the scan of the events table, and that is the step an index on completed would fix — if completed were selective, which it is not here.
What does USE TEMP B-TREE mean?
A grouping query that has to build a temporary sorted structure to do its work:
EXPLAIN QUERY PLAN
SELECT genre, COUNT(*) FROM movies GROUP BY genre;
QUERY PLAN
|--SCAN movies
`--USE TEMP B-TREE FOR GROUP BY
The engine cannot group by genre in table order, because the rows are stored by movie_id and genre is scattered across them. So it sorts first and groups second — an extra pass over the data, and extra memory. An index on the grouping column fixes it, because the index is already sorted by that column:
CREATE INDEX ix_movies_genre ON movies(genre);
EXPLAIN QUERY PLAN
SELECT genre, COUNT(*) FROM movies GROUP BY genre;
QUERY PLAN
`--SCAN movies USING COVERING INDEX ix_movies_genre
The temp b-tree has gone. This is the single most useful thing an index does for a reporting query, and it is invisible in the results — only the plan shows it.
What does a correlated subquery look like in a plan?
Correlated subqueries have their own line in the plan, and that line tells you the query runs the inner part once per outer row. The inner lookup needs an index of its own, so here is one on the join column:
CREATE INDEX ix_viewing_events_movie ON viewing_events(movie_id);
With that index in place, the plan for the correlated subquery reads like this:
EXPLAIN QUERY PLAN
SELECT m.title FROM movies m
WHERE m.rating > (SELECT AVG(v.minutes_watched) FROM viewing_events v WHERE v.movie_id = m.movie_id);
QUERY PLAN
|--SCAN m
`--CORRELATED SCALAR SUBQUERY 1
`--SEARCH v USING INDEX ix_viewing_events_movie (movie_id=?)
The CORRELATED SCALAR SUBQUERY line tells you the inner query is re-executed for every movie, and the SEARCH underneath tells you each execution is an index lookup rather than a scan. That is the ideal pairing — a correlated subquery that would be disastrous without an index, and cheap with one. On PostgreSQL and MySQL, running the plan with ANALYZE additionally reports the actual time and the actual row counts, which is how you find the difference between an estimated plan and a real one.
What should you look for in a plan?
- SCAN on a large table where you expected a SEARCH — the usual sign that a missing index is the problem.
- USE TEMP B-TREE — the engine is sorting or grouping on the fly, and an index may remove the pass.
- CORRELATED or LIST SUBQUERY — check whether it runs once per row, and whether the inner lookup is indexed.
- A SEARCH that is not COVERING — the engine finds the right row but then has to fetch it, which is an extra lookup per match.
- The order of the tables. The plan's order is the engine's, and it is not always the order you wrote.
One caveat before you start optimising: a plan describes what the engine intends, not what it will do. It can change with the data volume, after a statistic refresh, or between two runs of the same query if the data changed. That is why the professional habit is to look at the plan and the measured timings — in PostgreSQL with EXPLAIN ANALYZE, in MySQL with the profiling tools, in SQLite with the .timer command.
Quick reference
- EXPLAIN QUERY PLAN in SQLite, EXPLAIN in MySQL and PostgreSQL, execution plans in SQL Server.
- SCAN reads every row; SEARCH uses an index to jump to the matching rows.
- USE TEMP B-TREE means the engine is sorting or grouping on the fly.
- CORRELATED SUBQUERY means the inner query runs once per outer row.
- Read the plan from the inside out, and compare a plan before and after each change.
Plan drill: take one of your own queries and run EXPLAIN QUERY PLAN on it. Find the line you did not expect — the temp b-tree, the scan, the correlated subquery — and write one sentence describing what that line costs.