EXPLAIN QUERY PLAN — Read the Truth

~14 min · explain, query-plan, performance

Level 0Scout

0 XP0/80 lessons0/10 achievements

0/120 XP to next level120 XP to go0% complete

Stop guessing, ask the engine

Prefix any query with EXPLAIN QUERY PLAN and SQLite tells you what it actually intends to do. The output is a tree of operations; the words to look for:

SCAN — full table scan. Cheap on tiny tables, expensive on large ones.
SEARCH — index lookup (good).
USING INDEX X — which index was picked.
USING COVERING INDEX X — every column the query needs is in the index, so SQLite never touches the actual table (excellent — covered next lesson).
USE TEMP B-TREE FOR ORDER BY — SQLite couldn't use an index for sorting; it built a temporary one. Often a sign you're missing a sorted index.

Tip: EXPLAIN QUERY PLAN should be muscle memory. Before optimizing, explain. After optimizing, explain again to confirm the plan changed. A 'fix' that doesn't change the plan didn't fix anything.

Code

Reading the plan·sql

EXPLAIN QUERY PLAN
SELECT * FROM messages WHERE conversation_id = 1 ORDER BY created_at DESC LIMIT 20;

-- Without a useful index:
-- 0|0|0|SCAN messages
-- 0|0|0|USE TEMP B-TREE FOR ORDER BY

-- After CREATE INDEX idx_msg_conv_created ON messages(conversation_id, created_at DESC):
-- 0|0|0|SEARCH messages USING INDEX idx_msg_conv_created (conversation_id=?)

Plan-driven optimization workflow·bash

# 1. Identify the slow query
sqlite3 myapp.db 'EXPLAIN QUERY PLAN SELECT ...'

# 2. Add or adjust an index
sqlite3 myapp.db 'CREATE INDEX idx_... ON ...'

# 3. Re-explain to confirm the plan changed
sqlite3 myapp.db 'EXPLAIN QUERY PLAN SELECT ...'

# 4. Re-time to confirm the wall-clock improved
sqlite3 myapp.db '.timer on' 'SELECT ...'

External links

Exercise

Pick three slow-feeling queries from any of your apps. EXPLAIN QUERY PLAN each. Note whether SQLite is doing SCAN or SEARCH, whether ORDER BY uses a TEMP B-TREE, and whether composite indexes were used. For each, propose an index that would change the plan and try it.

EXPLAIN QUERY PLAN — Read the Truth

Stop guessing, ask the engine

Code

External links

Exercise

Progress

Comments 0