SQLite EXPLAIN QUERY PLAN

EXPLAIN QUERY PLAN shows exactly how SQLite plans to execute a query without actually running it. Use it to discover whether SQLite is using an index, performing a full table scan, or sorting in memory. This is the first tool to reach for when a query runs slowly.

Basic Syntax

EXPLAIN QUERY PLAN
SELECT ...;

Reading the Output

EXPLAIN QUERY PLAN
SELECT name FROM employees WHERE email = 'alice@x.com';

id │ parent │ notused │ detail
───┼────────┼─────────┼─────────────────────────────────────────────
2  │ 0      │ 0       │ SEARCH employees USING INDEX idx_email (email=?)

KEY TERMS:
──────────────────────────────────────────────────────────────────────
SCAN employees         → Full table scan (reads every row) — SLOW
SEARCH employees       → Index-based lookup (jumps to rows)  — FAST
USING INDEX idx_email  → Tells you which index is used
USING COVERING INDEX   → Index holds all needed data (fastest)
USE TEMP B-TREE FOR    → SQLite sorts in memory (no ORDER BY index)

Full Table Scan Example

-- No index on "name" column
EXPLAIN QUERY PLAN
SELECT name FROM employees WHERE name = 'Alice';

detail
───────────────────────────────────────────────────────────────
SCAN employees

"SCAN" = reading all rows — bad for large tables.
Fix: CREATE INDEX idx_name ON employees(name);

After adding the index:
EXPLAIN QUERY PLAN
SELECT name FROM employees WHERE name = 'Alice';

detail
───────────────────────────────────────────────────────────────
SEARCH employees USING INDEX idx_name (name=?)

Checking a JOIN Plan

EXPLAIN QUERY PLAN
SELECT e.name, d.name AS dept
FROM employees e
JOIN departments d ON e.dept_id = d.id
WHERE e.salary > 70000;

detail
───────────────────────────────────────────────────────────────────
SCAN employees
SEARCH departments USING INTEGER PRIMARY KEY (rowid=?)

Interpretation:
  employees: full scan (no index on salary)
  departments: fast PK lookup for each employee row

Fix: CREATE INDEX idx_salary ON employees(salary);

Sorting Without an Index

EXPLAIN QUERY PLAN
SELECT name FROM employees ORDER BY name;

detail
────────────────────────────────────────────
SCAN employees
USE TEMP B-TREE FOR ORDER BY

SQLite scans all rows then sorts them in memory.

After CREATE INDEX idx_name ON employees(name):
detail
────────────────────────────────────────────
SCAN employees USING INDEX idx_name

Now SQLite reads rows in index order — no sort step needed.

Subquery Plan

EXPLAIN QUERY PLAN
SELECT name FROM employees
WHERE dept_id IN (SELECT id FROM departments WHERE name='Engineering');

id │ parent │ detail
───┼────────┼──────────────────────────────────────────────────────
3  │ 0      │ SEARCH departments USING COVERING INDEX ... (name=?)
5  │ 0      │ SEARCH employees USING INDEX idx_dept (dept_id=?)

Covering Index Plan

A covering index contains every column a query needs. SQLite reads only the index and never touches the main table rows. This is the fastest possible plan because it avoids a second disk lookup for each result row.

-- Index on (name, salary) covers a query that only needs those two columns
CREATE INDEX idx_name_salary ON employees(name, salary);

EXPLAIN QUERY PLAN
SELECT name, salary FROM employees WHERE name = 'Alice';

detail
───────────────────────────────────────────────────────────────────────
SEARCH employees USING COVERING INDEX idx_name_salary (name=?)

"COVERING INDEX" means SQLite reads the index only — no table access.
This is faster than USING INDEX because it skips the extra table lookup.

Multi-Table JOIN Plan Reading

-- Three-table join
EXPLAIN QUERY PLAN
SELECT e.name, d.name, l.city
FROM employees e
JOIN departments d ON e.dept_id = d.id
JOIN locations   l ON d.location_id = l.id
WHERE e.salary > 60000;

id │ parent │ detail
───┼────────┼────────────────────────────────────────────────────────────
3  │ 0      │ SCAN employees (no salary index — full scan first)
5  │ 3      │ SEARCH departments USING INTEGER PRIMARY KEY (rowid=?)
7  │ 5      │ SEARCH locations USING INTEGER PRIMARY KEY (rowid=?)

Reading multi-table plans:
  parent=0 → first table in the join order (the driving table)
  parent=3 → processed after row 3's table
  parent=5 → processed after row 5's table

The driving table (parent=0) sets the pace. If it is slow,
the entire query is slow. Index the driving table's filter column.

EXPLAIN QUERY PLAN Workflow

Step 1: Run a slow query — note the execution time

Step 2: Prefix it with EXPLAIN QUERY PLAN
──────────────────────────────────────────
EXPLAIN QUERY PLAN
SELECT ... FROM ... WHERE ... ORDER BY ...;

Step 3: Look for SCAN on large tables → add an index

Step 4: Look for USE TEMP B-TREE → add index on ORDER BY column

Step 5: Add the index
──────────────────────────────────────────
CREATE INDEX idx_col ON table(col);

Step 6: Re-run EXPLAIN QUERY PLAN → confirm SEARCH appears

Step 7: Run the original query → measure new execution time

EXPLAIN vs EXPLAIN QUERY PLAN

EXPLAIN QUERY PLAN SELECT ...;
  → High-level plan: which tables and indexes SQLite will use
  → Human-readable, use this for day-to-day optimization

EXPLAIN SELECT ...;
  → Low-level virtual machine opcodes SQLite will execute
  → Detailed bytecode, useful only for deep SQLite internals study

For most developers, EXPLAIN QUERY PLAN is all you need.

Quick Reference: Plan Signals

OUTPUT CONTAINS               MEANING
────────────────────────────  ────────────────────────────────────────────
SCAN table                    Full table scan — consider adding index
SEARCH table USING INDEX      Index used for row lookup — good
USING COVERING INDEX          No table access needed — fastest
USE TEMP B-TREE FOR ORDER BY  In-memory sort — index on ORDER BY col helps
USE TEMP B-TREE FOR DISTINCT  In-memory dedup — check if needed
SCAN table USING INDEX        Index used for ordering, but all rows read

Key Takeaways

EXPLAIN QUERY PLAN SELECT ... shows the execution plan without running the query
SCAN means full table scan — often a sign to add an index on the WHERE or ORDER BY column
SEARCH USING INDEX confirms an index is being used for lookups
USING COVERING INDEX is the best outcome — no table row access needed at all
USE TEMP B-TREE FOR ORDER BY means SQLite sorts in memory — create an index on the ORDER BY column to eliminate this step
Always re-check the plan after adding an index to confirm it is actually being picked up

Previous lesson

Back to course

Next lesson