Reading a Query Plan
SQL: lesson 15 of 15
Stop guessing why it is slow — ask the engine what it did.
Lesson 15 of 15 · 6 min
Reading a Query Plan
Step 1 of 9
A join of two tables is slow. Rather than guess, ask the engine what it intends to do.
The Idea
A query plan is the engine's own account of what it will do: which table it reads first, whether it scans or searches, which index it uses, and whether it has to sort afterwards. Two words carry most of the meaning — SCAN reads everything, SEARCH jumps in.
Real-World Example
A courier's route sheet. It does not tell you the traffic; it tells you the order of the drops. When a delivery is late, that sheet shows the detour long before a stopwatch does.
The Code
import sqlite3
db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE kiln (id INT PRIMARY KEY, glaze TEXT);
CREATE TABLE firing (kiln_id INT, cone INT);
INSERT INTO kiln VALUES (1,'tenmoku'),(2,'celadon');
INSERT INTO firing VALUES (1,10),(1,8),(2,6);""")
q = """SELECT k.glaze, f.cone FROM firing f
JOIN kiln k ON k.id = f.kiln_id WHERE f.cone > 7"""
print([r[3] for r in db.execute("EXPLAIN QUERY PLAN " + q)])
# ['SCAN f', 'SEARCH k USING INDEX sqlite_autoindex_kiln_1 (id=?)']
db.execute("CREATE INDEX i_cone ON firing(cone)")
print([r[3] for r in db.execute("EXPLAIN QUERY PLAN " + q)][0])
# SEARCH f USING INDEX i_cone (cone>?)Your turn
Put the steps in the right order.
- Add the index the filtered column was missing
- Run EXPLAIN QUERY PLAN on the slow query
- Re-read the plan and confirm SCAN became SEARCH
- Find the line that says SCAN and note which table it names
Mini quiz
1 / 3