The EXPLAIN shows you the plan that the database intends to execute. It is the only tool that answers the question that every slow query eventually asks, which is "what is the engine actually doing, and is that what I assumed it would do?"
The previous lesson in this module covered "How Do Indexes Speed Reads and Slow Writes?", and the next lesson in this module is about "Which Columns Deserve an Index?".
A plan is not a score. It is a description, and reading it well is the difference between guessing and fixing.
How does the EXPLAIN reveal the plan of a query?
Every engine has a way to ask for the plan, and the spelling differs. SQLite uses the EXPLAIN QUERY PLAN. MySQL uses the EXPLAIN, and the EXPLAIN FORMAT=JSON for the detailed form. PostgreSQL uses the EXPLAIN with the optional ANALYZE. The SQL Server uses the SET STATISTICS IO, ON or the graphical execution plan.
All of them answer the same question.
How do you read a plan?
You start with the two words that matter most, which are the SCAN and the SEARCH.
A SCAN means that the engine reads every row of the table and it evaluates the condition on each one. A SEARCH means that 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=?)You read it from the inside out.
The engine scans the viewing_events, and it tests the completed flag on each of the twenty-four rows. For each row that passes, it looks up the user by the primary key and the movie by the 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 that an index on completed would fix, if completed were the selective one. Here it is not.
What does the USE TEMP B-TREE mean?
It means that a grouping query has to build a temporary sorted structure in order 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 BYThe engine cannot group by the genre in the table order, because the rows are stored by the movie_id and the genre is scattered across them. So it sorts first, and it groups second. That is an extra pass over the data, and it is 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_genreThe temp b-tree has gone.
This is the single most useful thing that 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 that the query runs the inner part once for every 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 that the inner query is re-executed for every movie, and the SEARCH underneath tells you that each execution is an index lookup and not a scan.
That is the ideal pairing. A correlated subquery that would be disastrous without an index, and it is cheap with one.
On PostgreSQL and on MySQL, running the plan with the ANALYZE additionally reports the actual time and the actual row counts. That is how you find the difference between an estimated plan and a real one.
What should you look for in a plan?
- A SCAN on a large table where you expected a SEARCH. It is the usual sign that a missing index is the problem.
- The USE TEMP B-TREE. The engine is sorting or grouping on the fly, and an index may remove the pass.
- The CORRELATED or the LIST SUBQUERY. Check whether it runs once for every row, and whether the inner lookup is indexed.
- A SEARCH that is not the COVERING one. The engine finds the right row, and then it has to fetch it, which is an extra lookup for every match.
- The order of the tables. The order in the plan is the engine's, and it is not always the order that you wrote.
There is one caveat before you start optimising. A plan describes what the engine intends and not what it will do. It can change with the volume of the data, after a refresh of the statistics, or between two runs of the same query if the data changed.
That is the reason that the professional habit is to look at the plan and at the measured timings. In PostgreSQL with the EXPLAIN ANALYZE, in MySQL with the profiling tools, and in SQLite with the .timer command.
Quick reference
- There is the
EXPLAIN QUERY PLANin SQLite, theEXPLAINin MySQL and in PostgreSQL, and the execution plans in the SQL Server. - A SCAN reads every row, and a SEARCH uses an index to jump to the matching rows.
- The USE TEMP B-TREE means that the engine is sorting or grouping on the fly.
- The CORRELATED SUBQUERY means that the inner query runs once for every outer row.
- You read the plan from the inside out, and you compare a plan before and after each change.
Try it yourself: take one of your own queries and run the EXPLAIN QUERY PLAN on it. Find the line that you did not expect, which is the temp b-tree, the scan, or the correlated subquery, and write one sentence that describes what that line costs.