SQLite CREATE INDEX: Choose and Check an Index

Index a filter

This fresh example has two matching rows, with ids 1 and 3. Run it once, then select only the SELECT and use Explain to inspect the index access. Small tables can be scanned efficiently too.

CREATE TABLE tickets(id INTEGER PRIMARY KEY,status TEXT,created_at TEXT); INSERT INTO tickets VALUES(1,'open','2026-01-01'),(2,'closed','2026-01-02'),(3,'open','2026-01-03'); CREATE INDEX tickets_status_date ON tickets(status,created_at); SELECT id,created_at FROM tickets WHERE status='open' ORDER BY created_at;

Choose column order deliberately

For a query filtering status and ordering by created_at, the composite (status, created_at) index is a useful candidate. Reversing those columns changes which access patterns it supports. A covering index includes the values needed by the query and can avoid a separate table lookup.

Compare the plan

The Query plan explorer can keep a baseline in memory. Explain the same SELECT before and after explicitly creating the index. A SEARCH typically means narrowed access; USE TEMP B-TREE can indicate additional sort/group work. Exact plan text is version dependent.

Account for costs

Indexes occupy storage and must be maintained when rows change. Review existing indexes before adding overlapping ones. UNIQUE indexes also enforce a constraint, so do not remove one solely because a query does not use it. Compare runtime on realistic data before retaining a performance index.