Description

Login
Back

Advanced · 18 min

Reading query plans

Inspect whether SQLite scans a table or searches an index.

Sample database

CREATE TABLE groups (id INTEGER PRIMARY KEY, label TEXT NOT NULL);
CREATE TABLE members (id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT, group_id INTEGER REFERENCES groups(id));
CREATE TABLE sessions (id INTEGER PRIMARY KEY, member_id INTEGER NOT NULL REFERENCES members(id), topic TEXT NOT NULL, minutes INTEGER NOT NULL CHECK(minutes > 0), completed INTEGER NOT NULL CHECK(completed IN (0, 1)), day TEXT NOT NULL);
INSERT INTO groups VALUES (1, 'Morning'), (2, 'Evening');
INSERT INTO members VALUES (1, 'Ada', 'Oslo', 1), (2, 'Bo', 'Rome', 1), (3, 'Cy', NULL, 2), (4, 'Dee', 'Oslo', 2);
INSERT INTO sessions VALUES
  (1, 1, 'Travel', 20, 1, '2026-01-01'),
  (2, 1, 'Food', 10, 0, '2026-01-02'),
  (3, 2, 'Travel', 30, 1, '2026-01-01'),
  (4, 2, 'Music', 15, 1, '2026-01-03'),
  (5, 3, 'Music', 25, 0, '2026-01-02'),
  (6, 3, 'Travel', 15, 1, '2026-01-04');

Inspect access paths

EXPLAIN QUERY PLAN reports the access strategy without executing the selected query. A scan is not always bad: for small tables or broad queries it can be reasonable. Plan wording is SQLite-specific and can change with engine versions.

EXPLAIN QUERY PLAN SELECT id FROM sessions WHERE member_id = 2;

Output

id | parent | notused | detail
2 | 0 | 216 | SCAN sessions

Compare an indexed query

Adding an index can enable a search by its leading column. A covering index contains everything the query needs. This tiny dataset teaches how to read a plan, not how to prove a production performance improvement.

CREATE INDEX sessions_member ON sessions(member_id);
EXPLAIN QUERY PLAN SELECT id FROM sessions WHERE member_id = 2;

Output

id | parent | notused | detail
2 | 0 | 52 | SEARCH sessions USING COVERING INDEX sessions_member (member_id=?)

Put it into practice

  1. Read the sample tables and predict the result before running the query.
  2. Solve the task, compare the returned rows, and explain why the solution works.

Try it yourself

Code runs on this device. When you are signed in, drafts sync to your account.

Run queries on a fresh sample database. Table changes last for this run only; your query draft is saved separately. Results show column names followed by rows. NULL means a missing value.

Apply what you learned

Create an index on session member_id and inspect the plan for selecting IDs belonging to member 2.

Show solution
CREATE INDEX sessions_member ON sessions(member_id);
EXPLAIN QUERY PLAN SELECT id FROM sessions WHERE member_id = 2;

The plan can search the covering member index. Actual performance still depends on data distribution and the workload.

Practice