Query Optimization & EXPLAIN
Creating an index doesn't guarantee SQLite actually uses it — and the only way to know for sure is to ask the query planner directly. EXPLAIN QUERY PLAN is SQLite's primary diagnostic tool for this: it prints a short, human-readable summary of how a query will be executed, so you can confirm it's hitting an index rather than scanning the whole table.
EXPLAIN QUERY PLAN
Put EXPLAIN QUERY PLAN in front of any query to see its plan instead
of running it. This is different from plain EXPLAIN, which dumps SQLite's raw low-level
VDBE bytecode — nearly unreadable unless you're debugging the engine itself.
EXPLAIN QUERY PLAN (the exact two-word phrase) is the one you want for
everyday optimization work.
EXPLAIN QUERY PLAN
SELECT rating, comment
FROM reviews
WHERE recipe_id = 42;Reading the Output: SEARCH vs. SCAN
The output boils down to one of two words per table touched:
| Output word | Meaning |
|---|---|
SCAN | Full table scan — SQLite reads every row in the table to find matches. Slow on large tables. |
SEARCH | SQLite is using an index (or the primary key) to jump directly to matching rows, shown as USING INDEX ... or USING PRIMARY KEY |
Before the index from the previous lesson exists, RecipeBox's review lookup does a full scan:
-- Before: no index on reviews.recipe_id
EXPLAIN QUERY PLAN
SELECT rating, comment FROM reviews WHERE recipe_id = 42;
-- Output:
-- QUERY PLAN
-- `--SCAN reviews-- After: CREATE INDEX idx_reviews_recipe_id ON reviews(recipe_id);
EXPLAIN QUERY PLAN
SELECT rating, comment FROM reviews WHERE recipe_id = 42;
-- Output:
-- QUERY PLAN
-- `--SEARCH reviews USING INDEX idx_reviews_recipe_id (recipe_id=?)SCAN reviews means every row in
reviews was examined. SEARCH reviews USING INDEX idx_reviews_recipe_id means
SQLite jumped straight to the matching rows — exactly the improvement the index was built for.Practical Optimization Tips
| Tip | Why it helps |
|---|---|
Index columns used in WHERE, JOIN, and ORDER BY | These are exactly the operations EXPLAIN QUERY PLAN checks for a SEARCH instead of a SCAN |
Avoid SELECT * when you only need a few columns | Less data read and returned per row, and some queries can be satisfied entirely from the index without touching the table at all |
Avoid wrapping an indexed column in a function inside WHERE | An expression like WHERE lower(title) = 'pie' prevents SQLite from using a plain index on title, since it would have to evaluate the function on every row first |
-- This prevents index use on title, forcing a SCAN:
SELECT * FROM recipes WHERE lower(title) = 'apple pie';
-- Better: compare directly, or store/index a normalized column,
-- and only select the columns you actually need
SELECT id, title FROM recipes WHERE title = 'Apple Pie';