SQLite EXPLAIN and EXPLAIN QUERY PLAN Guide
For someone who writes SQL against a SQLite file and wants to know whether a query used the index; the plan output and the two EXPLAIN forms are explained where they appear.
On this page
You created an index, the query still pauses, and the SQL itself says nothing about why. Put the words EXPLAIN QUERY PLAN in front of the SELECT and SQLite prints one line per table it reads: SCAN when it walks every record, SEARCH when it visits only a subset. The bare EXPLAIN keyword answers a different question, returning the virtual machine program the statement compiles to, one row per instruction.
The EXPLAIN QUERY PLAN examples in the SQLite documentation read two tables, declared here so the plans below have something concrete behind them:
CREATE TABLE t1(a, b);
CREATE TABLE t2(c, d);
What SCAN and SEARCH tell you
For each table a query reads, the plan carries a line that begins with SCAN or SEARCH. The SQLite documentation defines the two: SCAN is a full-table scan, including the case where SQLite walks every record in an order an index defines, and SEARCH means only a subset of the rows is visited. Each line also names the table, says whether an index or an automatic index is used, says whether the covering index optimization applies, and shows which WHERE clause terms did the indexing.
With no index on the column in the WHERE clause, the whole table is read:
EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1;
QUERY PLAN
`--SCAN t1
Create an index on the column and the same query visits a subset. The plan names the index and the term it satisfied:
CREATE INDEX i1 ON t1(a);
EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1;
QUERY PLAN
`--SEARCH t1 USING INDEX i1 (a=?)
An index that holds every column the query asks for goes one step further. SQLite answers out of the index alone and never touches the table, which the plan spells out as COVERING INDEX:
CREATE INDEX i2 ON t1(a, b);
EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1;
QUERY PLAN
`--SEARCH t1 USING COVERING INDEX i2 (a=?)
Those three lines are the whole reason to run EXPLAIN QUERY PLAN after adding an index: SEARCH with the index name means the index is doing work, and SCAN means it is not.
Reading a join and a sort in the plan
SQLite implements every join as nested scans, so a two-table query produces two lines, one per nested loop:
EXPLAIN QUERY PLAN SELECT t1.*, t2.* FROM t1, t2 WHERE t1.a=1 AND t1.b>2;
QUERY PLAN
|--SEARCH t1 USING INDEX i2 (a=? AND b>?)
`--SCAN t2
The order of the lines is the nesting order, so t1 is the outer loop and t2 the inner one. Reverse t1 and t2 in the FROM clause and the plan comes back identical, because it reports how SQLite evaluates the query rather than how you wrote it.
A sort shows up as its own line. When an ORDER BY, GROUP BY or DISTINCT clause forces SQLite to sort rows itself, the plan adds USE TEMP B-TREE FOR, followed by the clause that caused it:
EXPLAIN QUERY PLAN SELECT c, d FROM t2 ORDER BY c;
QUERY PLAN
|--SCAN t2
`--USE TEMP B-TREE FOR ORDER BY
An index on the sorted column removes the sort, because SQLite can read the rows in order instead of collecting and sorting them. The line disappears from the plan:
CREATE INDEX i4 ON t2(c);
EXPLAIN QUERY PLAN SELECT c, d FROM t2 ORDER BY c;
QUERY PLAN
`--SCAN t2 USING INDEX i4
Using an index here is almost always much more efficient than sorting, which makes a stray USE TEMP B-TREE line on a query you run often worth an index.
How EXPLAIN differs from EXPLAIN QUERY PLAN
Both keywords turn the statement into a query that reports what the statement would have done. What they report differs:
| Keyword | What it returns |
|---|---|
EXPLAIN QUERY PLAN | High-level information about the query plan, table by table |
EXPLAIN | The sequence of virtual machine instructions the statement compiles to |
EXPLAIN output has one row per instruction, with the address, the opcode, up to five operands named p1 to p5, and a comment column. The comment is only there when SQLite was compiled with -DSQLITE_ENABLE_EXPLAIN_COMMENTS, so the same statement on two builds can print a narrower table. Reading that program means knowing the bytecode opcodes, which is why EXPLAIN QUERY PLAN is the one to reach for when the question is about indexes.
Neither format is a contract. The SQLite documentation on the EXPLAIN keyword states that the output from EXPLAIN and EXPLAIN QUERY PLAN is intended for interactive analysis and troubleshooting only, that the details are subject to change from one release to the next, and that applications should not use either keyword. The EXPLAIN QUERY PLAN format did change substantially in version 3.24.0 and again, more mildly, in 3.36.0. Read the plans, act on them, and keep them out of your test assertions.
Running EXPLAIN QUERY PLAN in the sqlite3 shell
Start the shell, then open a database file with the .open command. If you have no file yet, our article on creating a SQLite database covers that, and the one on creating a table covers the tables to put in it.
sqlite3
.open mydatabase.db
Prefixing every statement gets old, so the shell has a mode that does it for you. Turn it on and each statement you enter is preceded by its plan:
.eqp on
The shell renders the plan as the ASCII-art tree shown above. Underneath, EXPLAIN QUERY PLAN returns an ordinary four-column table, and .explain off displays it that way instead, which is what you want when you pipe the output somewhere. .explain auto restores the tree.
EXPLAIN QUERY PLAN is most useful on a SELECT, and it also works on the other statements that read data from tables, such as UPDATE, DELETE, and INSERT INTO ... SELECT. One family is unaffected: some PRAGMA statements do their work while the statement is prepared rather than while it runs, and EXPLAIN reports nothing about those.
EXPLAIN QUERY PLAN in the DbSchema SQL Editor
DbSchema connects to a SQLite file and reverse-engineers it into a diagram, and its SQL Editor takes the statements above unchanged. Open the editor from the Editors menu or the toolbar, type the query with the EXPLAIN QUERY PLAN prefix, and press Execute Query. DbSchema shows the four columns SQLite returns as a result table, so you read the plan as rows with a parent column rather than as an indented tree. Every statement you run lands in the SQL History pane, which is where to find the plan you ran three queries ago.
The SQL Editor sends its statements to the connected SQLite database and reads the answer back. It changes nothing in the DbSchema model file, which holds the diagram and the saved editors rather than table data. Both the SQL Editor and the diagrams are in the free Community Edition.
Run EXPLAIN QUERY PLAN before and after every index you add, and the plan tells you in one line whether the index earned its place: SEARCH names it, SCAN ignores it, and a USE TEMP B-TREE line names a sort still waiting for an index of its own. To read those plans next to the diagram of the schema they run against, download DbSchema at https://dbschema.com/download.html, connect to your SQLite file, and open the SQL Editor from the Editors menu; both are in the free Community Edition.
