SQLite Basics: Inspect Indexes with EXPLAIN QUERY PLAN

October 20, 2026

An index gives SQLite another way to find rows. EXPLAIN QUERY PLAN shows the chosen access strategy. Use these small examples to read a plan before measuring performance with representative data.

Prepare a scratch directory

Tested on Linux with Python 3.12.14 and SQLite 3.53.1. You need Bash, Python 3 with SQLite support, a text editor, and mktemp, rm, and rmdir. No additional Python packages are needed. Keep the same terminal open and save files as UTF-8.

start_dir="$PWD"
work_dir="$(mktemp -d)" || exit 1
cd -- "$work_dir" || exit 1

The standalone SQLite CLI was unavailable on the test machine. Save this runner as run-sql.py. Each invocation uses a fresh in-memory database and prints pipe-separated rows, displaying missing values as NULL. These toy databases disappear when the connection closes. Put each statement’s ending semicolon at the end of a line, as shown; this is a teaching runner, not a general SQL shell or CSV exporter.

from pathlib import Path
import sqlite3
import sys

connection = sqlite3.connect(":memory:", isolation_level=None)
try:
    statement = ""
    for line in Path(sys.argv[1]).read_text(encoding="utf-8").splitlines(True):
        statement += line
        if sqlite3.complete_statement(statement):
            cursor = connection.execute(statement)
            for row in cursor:
                print("|".join("NULL" if value is None else str(value) for value in row))
            statement = ""
    if statement.strip():
        raise ValueError("Unfinished SQL statement")
finally:
    connection.close()

The plan rows below are the exact raw output from SQLite 3.53.1 through this runner. Focus on their final description column. Plan wording, numeric fields, and choices can change with SQLite versions, statistics, and data; do not parse this diagnostic output as a stable application interface.

1. Compare a scan with an indexed lookup

Save as lookup-plan.sql:

CREATE TABLE books (id INTEGER PRIMARY KEY, code TEXT, title TEXT);
INSERT INTO books VALUES (1, 'B01', 'Linux'), (2, 'B02', 'SQLite'), (3, 'B03', 'Python');
EXPLAIN QUERY PLAN SELECT title FROM books WHERE code = 'B02';
CREATE INDEX books_code ON books(code);
EXPLAIN QUERY PLAN SELECT title FROM books WHERE code = 'B02';
SELECT title FROM books WHERE code = 'B02';

Run:

python3 run-sql.py lookup-plan.sql

Output:

2|0|216|SCAN books
3|0|62|SEARCH books USING INDEX books_code (code=?)
SQLite

The first plan scans books. The second searches books_code for matching codes, then reads the title from the table. EXPLAIN QUERY PLAN describes the query; the final ordinary SELECT actually returns its result.

2. Support filtering and ordering together

Save as ordered-plan.sql:

CREATE TABLE tickets (id INTEGER PRIMARY KEY, status TEXT, created_at TEXT, title TEXT);
INSERT INTO tickets VALUES
    (1, 'open', '2026-10-19', 'Fix login'),
    (2, 'closed', '2026-10-18', 'Update docs'),
    (3, 'open', '2026-10-18', 'Check backup'),
    (4, 'open', '2026-10-19', 'Review logs');
CREATE INDEX tickets_status_date ON tickets(status, created_at, id);
EXPLAIN QUERY PLAN
SELECT id, title FROM tickets WHERE status = 'open' ORDER BY created_at, id;
SELECT id, title FROM tickets WHERE status = 'open' ORDER BY created_at, id;

Run:

python3 run-sql.py ordered-plan.sql

Output:

4|0|63|SEARCH tickets USING INDEX tickets_status_date (status=?)
3|Check backup
1|Fix login
4|Review logs

With status fixed, the index can provide rows in date-and-ID order. This plan has no separate temporary sort. The ID breaks date ties, and the title still comes from the table. Keep the explicit ORDER BY even when an index supplies that order.

3. Recognize a covering index

Save as covering-plan.sql:

CREATE TABLE readings (device TEXT, recorded_at TEXT, value INTEGER, note TEXT);
INSERT INTO readings VALUES
    ('A', '2026-10-20T09:00:00', 20, 'first'),
    ('B', '2026-10-20T09:00:00', 30, 'other'),
    ('A', '2026-10-20T10:00:00', 22, 'second');
CREATE INDEX readings_lookup ON readings(device, recorded_at, value);
EXPLAIN QUERY PLAN
SELECT recorded_at, value FROM readings WHERE device = 'A' ORDER BY recorded_at;
SELECT recorded_at, value FROM readings WHERE device = 'A' ORDER BY recorded_at;

Run:

python3 run-sql.py covering-plan.sql

Output:

3|0|56|SEARCH readings USING COVERING INDEX readings_lookup (device=?)
2026-10-20T09:00:00|20
2026-10-20T10:00:00|22

COVERING means this index holds every column needed by this query, so SQLite can obtain the result without reading the base table. Adding note to the SELECT would remove that advantage with this particular index.

These tiny datasets demonstrate plan shapes, not a speed benchmark. Indexes take storage and add maintenance work when rows change; test a real workload before adding many of them. A SCAN is not automatically a problem, and a SEARCH is not a timing measurement. See SQLite’s EXPLAIN QUERY PLAN guide, query-planning guide, and CREATE INDEX reference.

Clean up

In the same terminal, remove only these example files and the empty scratch directory:

rm -- run-sql.py lookup-plan.sql ordered-plan.sql covering-plan.sql
cd -- "$start_dir" || exit 1
rmdir -- "$work_dir"
unset start_dir work_dir

Acknowledgements

This post was created with assistance from AI.

Last updated on October 20, 2026

SQLite Basics: Reuse Queries with Views SQLite Basics: Combine and Compare Lists with Set Operations