Every query you write is translated by the database into a step-by-step execution plan. On fifteen rows the plan does not matter — everything is instant. On fifteen million rows the difference between a good plan and a bad one is the difference between a snappy report and a timeout that kills your application.
EXPLAIN QUERY PLAN is SQLite's window into that decision. It shows whether the engine will read every row in a table or jump straight to the ones you need. It reveals hidden sorts, temporary tables, and join orders that you never asked for but must pay for. Learning to read the plan is the first skill every database tuner acquires, because it turns guessing into diagnosis and lets you prove that an index actually helped.
flowchart LR S["SQL query"] --> P["Planner"] P -->|"chooses scans & searches"| E["Executor"] E --> R["Results"] style P fill:#0e7490,color:#fff
The vocabulary of a plan
SQLite's EXPLAIN QUERY PLAN returns a small table where the detail column tells the story. The two phrases you will see most often are SCAN TABLE and SEARCH TABLE USING INDEX.
A SCAN TABLE message means the engine will visit every row in the table, checking your WHERE clause as it goes. That is reliable but slow on large tables because the cost grows linearly with every row you add. A SEARCH TABLE USING INDEX message means the engine will walk the B-tree index directly to the qualifying rows and touch only those. That is dramatically faster when the filter is selective.
You will also see USE TEMP B-TREE FOR ORDER BY, which means SQLite must build a temporary structure to sort your results because no index provided them in the right order. Every temp b-tree is a hint that adding an index on the sort column could remove an expensive sorting step.
EXPLAIN QUERY PLAN SELECT name, salary FROM employees WHERE salary > 700000;
Changing the plan with an index
The scan above is forced because SQLite has no ordered map of salary values. It cannot know where the high-salary rows live, so it must inspect every employee from top to bottom. Once you build an index on the filtered column, the same query can switch from a scan to a search.
Watch the detail column change when an index is present. The engine now knows exactly where the qualifying rows live and can skip the rest of the table entirely. This is not a syntax change — the SQL is identical. The only difference is the invisible structure underneath the data, and that difference is only visible through EXPLAIN QUERY PLAN. Always verify with the plan rather than assuming an index is being used.
flowchart TD Q["need rows WHERE salary > 700000"] --> S["SCAN TABLE employees<br/>read all 15 rows"] Q --> I["SEARCH TABLE employees<br/>USING INDEX idx_salary"] S -->|"slow on big tables"| D["discards most rows"] I -->|"fast at any size"| K["keeps only matches"] style S fill:#b45309,color:#fff style I fill:#0e7490,color:#fff
CREATE INDEX idx_emp_salary ON employees (salary); EXPLAIN QUERY PLAN SELECT name, salary FROM employees WHERE salary > 700000;
Plans for joins
When you join two tables, the plan shows one step for each table, and the order they appear is the order SQLite chose to access them. The planner usually picks the smaller table to scan and searches the larger one via an index. If neither table has a useful index, you may see two scans and a warning about a Cartesian product, which means every row of the first table is paired with every row of the second before the ON clause throws the mismatches away.
Look for SEARCH TABLE ... USING INTEGER PRIMARY KEY, which appears when SQLite follows the rowid or PRIMARY KEY directly. That is the fastest possible lookup because there is no secondary structure to traverse. If a join plan shows two scans, treat it as a red flag. The result set may explode exponentially, and the only fix is an index on the column inside the ON condition.
flowchart LR B["SCAN TABLE employees"] --> M["MATCH rows with ON clause"] D["SEARCH TABLE departments<br/>USING INTEGER PRIMARY KEY"] --> M style B fill:#b45309,color:#fff style D fill:#0e7490,color:#fff
EXPLAIN QUERY PLAN SELECT e.name, d.name FROM employees e JOIN departments d ON e.department_id = d.id;
What the plan does not show
EXPLAIN QUERY PLAN describes access methods, not absolute time. A scan on a tiny lookup table may finish in microseconds while a search on a bloated index takes milliseconds. The plan tells you the shape of the work, not its exact cost.
Use the plan to answer structural questions: Is an index being used? Is there an unexpected sort? Is a join exploding into a Cartesian product? Combine those answers with the table size and the query frequency, and you have a clear priority list for what to fix first.
Building the habit
Do not wait for a slowdown to check your plans. The productive habit is to run EXPLAIN QUERY PLAN on every new query before it ships, while the table is still small. A scan that is harmless today will become your bottleneck tomorrow, and fixing it early means you can add the index before users complain.
Start with the queries that run most often — the dashboard that loads every page view, the search that runs on every keystroke. Those are the ones where a scan hurts most. A single index on the right column can turn a thousand-row scan into a three-row search, and the plan is how you prove it.
Return the name and salary of every employee who earns more than 600000, sorted from highest to lowest.
SELECT name, salary FROM employees -- add your filter and sort ;
Filter with WHERE salary > 600000.
Highest first means ORDER BY salary DESC.
SELECT name, salary FROM employees WHERE salary > 600000 ORDER BY salary DESC;
Return the name of each department that has at least one employee with a salary above 800000. Use a JOIN.
SELECT d.name FROM departments d -- join to employees and filter ;
Join departments to employees on department_id.
Use DISTINCT so each department appears once.
Filter with e.salary > 800000.
SELECT DISTINCT d.name FROM departments d JOIN employees e ON d.id = e.department_id WHERE e.salary > 800000;
The query SELECT name FROM employees WHERE role = 'Engineer' performs a full table scan because there is no index on role. Create an index named idx_emp_role so the engine can search instead.
CREATE INDEX idx_emp_role ;
The pattern is CREATE INDEX name ON table (column).
The table is employees and the column is role.
CREATE INDEX idx_emp_role ON employees (role);
You run EXPLAIN QUERY PLAN and see SCAN TABLE employees. What does this tell you?
A scan reads the entire table sequentially, checking the WHERE clause on each row. It is the fallback when no index can speed up the lookup.
Write a query that returns each department's name and the average salary of its employees, but only for departments whose average is strictly greater than 650000. Order the result from highest average to lowest.
SELECT d.name, AVG(e.salary) AS avg_salary FROM departments d JOIN employees e ON d.id = e.department_id -- add grouping and filtering ORDER BY avg_salary DESC ;
Use GROUP BY d.id, d.name to compute per-department averages.
Filter groups with HAVING AVG(e.salary) > 650000.
ORDER BY avg_salary DESC sorts highest first.
SELECT d.name, AVG(e.salary) AS avg_salary FROM departments d JOIN employees e ON d.id = e.department_id GROUP BY d.id, d.name HAVING AVG(e.salary) > 650000 ORDER BY avg_salary DESC;
Recap
- EXPLAIN QUERY PLAN reveals the database's execution strategy before the query runs.
- SCAN TABLE reads every row sequentially; SEARCH TABLE USING INDEX jumps straight to matches via the B-tree.
- Adding an index can turn a scan into a search without changing the SQL at all.
- Join plans show one step per table; two scans may signal a Cartesian product that explodes row counts.
- A search is only a win when it narrows the result to a small fraction of the table.
Next you will learn to choose which columns deserve indexes and how to build composite indexes that match the queries your application actually runs.