How can I use EXPLAIN QUERY PLAN to inspect a SQLite index choice?

Add EXPLAIN QUERY PLAN immediately before a SELECT statement. SQLite then gives a high-level description of its strategy, including information about table reads and index use. [S1]

Here’s a small, disposable example. Assume a tasks table with columns id, name, and status; the table and data are placeholders for a test database.

EXPLAIN QUERY PLAN
SELECT id, name
FROM tasks
WHERE status = 'open';

Inspect the plan before creating an index. Then try a candidate index and run the same plan query again:

CREATE INDEX idx_tasks_status ON tasks(status);

EXPLAIN QUERY PLAN
SELECT id, name
FROM tasks
WHERE status = 'open';

Compare what SQLite reports for the table read. The details commonly distinguish SCAN from SEARCH and may identify an index and the WHERE terms used for indexing. [S1] The exact output is illustrative, not something to copy or depend on; interpret the meaning rather than matching a particular line.

SCAN and SEARCH describe different table-read strategies. If the detail names idx_tasks_status, that indicates the displayed plan uses that index for this query. The detail may also indicate whether an index is covering or which WHERE terms are used for indexing. [S1]

For the example, you might see a detail resembling SEARCH tasks USING INDEX idx_tasks_status (status=?). That is only an illustration of the kind of information to look for—not a promised output string. SQLite’s plan text is intended for interactive debugging, and its format may change between releases. [S1]

A plan describes SQLite’s chosen strategy; don’t treat one word or an index name as a guarantee about how the query will behave in your particular use. The documentation describes what the plan can show, but does not promise that adding an index changes the plan or improves performance for every query and database.

Don’t assume that adding an index must change the plan. SQLite’s choice depends on the query and database, so a new index may not be selected. [S1] Check that you compared the same SELECT, including its WHERE clause, and that the candidate index is on a column relevant to the condition you’re investigating. Those checks make the comparison clearer; they do not guarantee a different plan.

For the status = 'open' example, CREATE INDEX idx_tasks_status ON tasks(status) is a candidate to inspect because it indexes the filtered column. Run the plan before and after creating it, then compare the reported table-read details. If they remain similar, record that result rather than changing the query just to make the output say SEARCH. The plan alone does not promise which alternative performs better for your data and workload.

Use this compact record in a scratch database or other disposable test setup. Replace the placeholders with your table, query, and candidate index; the example names are not facts about your schema.

Query being tested: [the unchanged SELECT statement]
Candidate index: [index name and indexed column(s)]
Before: [briefly note SCAN/SEARCH and any index named]
After:  [briefly note SCAN/SEARCH and any index named]
Conclusion: [what changed, or that the plan looked similar]

Keep the comparison focused on what the plan reports, not an exact text match. EXPLAIN QUERY PLAN provides information about strategy and index use, and SQLite warns that the display format can change. [S1] It does not promise a particular plan, a performance gain, or stable output wording across SQLite releases. To decide whether a change helps your application, evaluate it in the context of your own data and workload rather than relying on plan text alone.