In SQLite, how do you check whether a query uses an index, and what separates SCAN from SEARCH in EXPLAIN QUERY PLAN?
answer
- a prefix, not a run
- one line per table read
- every row versus a subset
- index name plus (col=?) terms
basics
~20 sPrefix the statement with EXPLAIN QUERY PLAN. A line starting SEARCH means SQLite visits only a subset of a table's rows through an index or the rowid; SCAN means a full pass over the table, even when it walks rows in an index's order.
solid answer
~40 sI put `EXPLAIN QUERY PLAN` in front of the statement, or type `.eqp on` in the `sqlite3` shell, and SQLite describes the plan it would use instead of running the query. Each table read gets one line. `SEARCH contacts USING INDEX contacts_surname_city (surname=? AND city=?)` means it seeks into that index, and the parenthesis lists the WHERE terms the index resolved. `SEARCH contacts USING INTEGER PRIMARY KEY (rowid=?)` is a direct rowid lookup. `SCAN contacts` is a full-table scan, and `SCAN contacts USING INDEX ...` still reads every row, just in index order, usually to skip a sort. In a join the lines are nested loops, outermost first. The output is meant for debugging and its wording changes between releases, so I read it rather than parse it.
code
console · 5 linessqlite> .eqp on
sqlite> SELECT * FROM contacts WHERE id = 42;
QUERY PLAN
`--SEARCH contacts USING INTEGER PRIMARY KEY (rowid=?)
sqlite> .eqp offgo deeper
Know the EXPLAIN QUERY PLAN prefix and the shell's .eqp on, and be able to say that SCAN is a full pass over the table while SEARCH reads a subset through an index or rowid.
Read the whole line: which index, whether it is covering, and which WHERE terms appear in the parenthesis. Explain why SCAN USING INDEX is still a full pass.
Use plans as evidence when a screen gets slow on real data: find the scanning loop in a join, check bound-value effects, and confirm each index change by re-reading the plan.
Decide how a team catches plan regressions: plan checks in tests against realistic data, and statement-status counters in production rather than parsing a format SQLite does not promise to keep.
## What EXPLAIN QUERY PLAN does Putting `EXPLAIN QUERY PLAN` in front of a `SELECT` (or an `UPDATE`, a `DELETE` or an `INSERT INTO ... SELECT`) makes SQLite return a description of the **plan** it would use, instead of running the statement. The **query planner** is the part of SQLite that decides, for every table a statement reads, whether to walk all of its rows or to jump straight to the ones it needs, either through an **index** (a separate B-tree kept sorted on chosen columns) or through the table's **rowid**, the integer key every ordinary SQLite table is stored under. In the `sqlite3` command-line shell the answer is drawn as a small tree. Two shell commands help: - `.eqp on` prints the plan before running every statement you type, and `.eqp off` turns that off again. - `.explain off` shows the raw form, four columns per node (node id, parent id, an unused field and the detail text), instead of the tree. ## SCAN versus SEARCH Every table the statement reads produces one line whose detail text begins with `SCAN` or `SEARCH`. | Line | What SQLite does | |---|---| | `SCAN contacts` | Reads every row of the table, a full-table scan (a LIMIT can end it early) | | `SCAN contacts USING INDEX i` | Still reads every row, but in the order of index `i`, usually so an ORDER BY needs no sort | | `SCAN contacts USING COVERING INDEX i` | Reads every entry of the index and never touches the table rows | | `SEARCH contacts USING INDEX i (surname=? AND city=?)` | Seeks into the index and visits only the matching subset | | `SEARCH contacts USING INTEGER PRIMARY KEY (rowid=?)` | Looks the row up directly by its rowid | The parenthesis on a `SEARCH` line lists the WHERE-clause terms the index resolved. A `?` stands for the value whether the query used a literal or a bound parameter, and range terms print as `>` and `<` even when the query wrote `>=` or `<=`. When a term you expected is missing from that list, the index is not narrowing on it. ## Reading the contacts example Take a phone app whose contacts screen searches by surname and city and has become slow: ```console sqlite> EXPLAIN QUERY PLAN SELECT * FROM contacts WHERE surname = 'Novak' AND city = 'Brno'; QUERY PLAN `--SCAN contacts sqlite> CREATE INDEX contacts_surname_city ON contacts(surname, city); sqlite> EXPLAIN QUERY PLAN SELECT * FROM contacts WHERE surname = 'Novak' AND city = 'Brno'; QUERY PLAN `--SEARCH contacts USING INDEX contacts_surname_city (surname=? AND city=?) ``` - Before the index, `SCAN contacts` means every row is read and tested against both conditions. - After it, `SEARCH` with both terms in the parenthesis means SQLite seeks straight to the matching entries. - A query on `city` alone still shows `SCAN contacts`, because that index is sorted by `surname` first. Analyzed statistics can change that through a skip-scan, which prints as `(ANY(surname) AND city=?)`. - `SELECT * FROM contacts ORDER BY surname` shows `SCAN contacts USING INDEX contacts_surname_city`: every row, read in surname order so no sort is needed. ## Joins, subqueries and the other lines - SQLite runs joins as **nested loops** and prints one line per loop. The **first line is the outermost loop**, whatever order the FROM clause lists the tables in. - `MULTI-INDEX OR` with child `SEARCH` lines means an OR was answered by searching more than one index and combining the results. - `USE TEMP B-TREE FOR ORDER BY` (or `GROUP BY`, or `DISTINCT`) means SQLite sorts rows in a temporary B-tree because no index delivers them in that order. - `CO-ROUTINE`, `MATERIALIZE`, `SCALAR SUBQUERY` and `CORRELATED SCALAR SUBQUERY` describe how subqueries run. A correlated one is re-run for every row of the outer query. - `USING AUTOMATIC COVERING INDEX` names a transient index SQLite builds for this one statement and then discards. ## What the output does not tell you - **No row estimates and no timings.** Row estimates disappeared from the output in 3.8.0, and SQLite has no EXPLAIN ANALYZE statement. The shell's `.scanstats on` reports per-loop counts only in builds compiled with `SQLITE_ENABLE_STMT_SCANSTATUS`. - **The wording is not stable.** The format changed substantially in 3.24.0 and again in 3.36.0, so the older SQLite bundled with a phone's operating system can word lines differently (older releases printed lines such as `SCAN TABLE contacts`). Application code should not parse it. To catch full scans and sorts at run time, `sqlite3_stmt_status()` with `SQLITE_STMTSTATUS_FULLSCAN_STEP` and `SQLITE_STMTSTATUS_SORT` is the supported route. - **Bound values can matter.** A plan taken with an unbound `?` can differ from the one the app gets once a value is bound, which matters for partial indexes. ## A quick routine 1. Copy the exact SQL the app runs, with the same WHERE and ORDER BY. 2. Run it under `EXPLAIN QUERY PLAN` against a database with realistic data. 3. Look for `SCAN` lines on large tables and for `SEARCH` lines whose parenthesis lacks a term you filtered on. 4. Change one index, re-run the plan and compare.
- Why does `surname LIKE 'Nov%'` still show SCAN in SQLite when contacts has an index on surname?SQLite's LIKE ignores ASCII case by default, so it can turn a prefix pattern into an index range only when the column is indexed with the `NOCASE` collation, or with the default `BINARY` collation if `PRAGMA case_sensitive_like` is on. The pattern must also be a literal or bound string that does not start with a wildcard. With `CREATE INDEX ... ON contacts(surname COLLATE NOCASE)` the plan becomes `SEARCH ... (surname>? AND surname<?)`.
- In SQLite's EXPLAIN QUERY PLAN for a two-table join, how do you tell which table is the outer loop?SQLite implements every join as nested loops and prints one SCAN or SEARCH line per loop in nesting order: the first line is the outermost loop and the last is the innermost. The order reflects how SQLite evaluates the join, not the order of tables in the FROM clause, so swapping them in the SQL often leaves the plan unchanged.
- How can a shipped SQLite app notice slow plans without parsing EXPLAIN QUERY PLAN text?The documentation says applications should not depend on the EXPLAIN QUERY PLAN format, which changes between releases. Instead, read `sqlite3_stmt_status()` counters after a statement runs: `SQLITE_STMTSTATUS_FULLSCAN_STEP` counts steps through a full scan and `SQLITE_STMTSTATUS_SORT` counts sort operations. Non-zero values on a hot query point at a missing index.
- SCAN ... USING INDEX means the index narrowed down the rows read.
- EXPLAIN QUERY PLAN runs the query and reports how long each step took.
- A lookup by INTEGER PRIMARY KEY shows up as a SCAN line.
- The plan lines follow the order of tables in the FROM clause.
- App code can parse the plan text safely because its format never changes.
Assumes SQLite 3.53; the EXPLAIN QUERY PLAN wording changed in 3.24.0 and 3.36.0, so an older bundled SQLite may print lines differently.