Chapter 10 · Indexes and the SQLite Query Planner
Composite Indexes, Leftmost Prefixes, Sorting, and Covering Indexes
Design composite indexes from real WHERE and ORDER BY patterns, reason about usable prefixes, observe covering-index plans, and remove redundant indexes rather than accumulating them.
Learning outcomes
A composite index is not “several single-column indexes bundled together.” Its keys have a deliberate lexicographic order. That order determines which WHERE constraints can narrow the search, whether ORDER BY can be satisfied without a separate sort, and whether requested output columns are already present in the index. This lesson derives column order from FieldNotes queries and checks every claim with EXPLAIN QUERY PLAN.
Design multi-column indexes from equality/range/order access patterns rather than generic slogans.
Explain the leftmost usable prefix concept and where a gap or range changes later-column usefulness.
Show when one composite index can help both filtering and ordering.
Recognize a covering-index plan and explain why table-row lookups can be avoided.
Detect prefix-redundant indexes and weigh them against genuinely different sort/collation requirements.
Use EQP for every indexing claim instead of inferring behavior from DDL alone.
Start with the query contract
FieldNotes often asks: “show this device’s newest notes.” The useful facts are not merely that the query mentions device_id and occurred_at. It constrains device equality and wants time ordering.
SELECT note_id, occurred_at, status, summaryFROM maintenance_noteWHERE device_id = 42ORDER BY occurred_at DESCLIMIT 20;A natural candidate is (device_id, occurred_at DESC): rows are first grouped by device key, then ordered by time within each device.
CREATE INDEX idx_note_device_timeON maintenance_note(device_id, occurred_at DESC);EXPLAIN QUERY PLANSELECT note_id, occurred_at, status, summaryFROM maintenance_noteWHERE device_id=42ORDER BY occurred_at DESCLIMIT 20;Look for a SEARCH using the composite index and, importantly, the absence of a separate USE TEMP B-TREE FOR ORDER BY node. That is evidence that the index ordering contributes to the result order.
Leftmost usable prefix: a navigation model
For an index on (device_id, occurred_at, priority), the key order is first by device, then by time among equal device values, then by priority among equal device/time values. SQLite can usually use a contiguous left-side set of useful constraints to narrow a range. A missing unconstrained leading column means later columns cannot normally be used as a simple direct lookup—although later statistics-driven optimizations such as skip-scan can sometimes change that, as Lesson 5 shows.
| Query predicate | Useful part of index (device_id, occurred_at, priority) | Reason |
|---|---|---|
device_id=? | device_id | Leftmost equality. |
device_id=? AND occurred_at>=? | device_id, occurred_at | Equality then range. |
device_id=? AND occurred_at=? AND priority>=? | All three can participate. | No gap before the final range. |
occurred_at>=? only | Usually not a direct lookup on the later column. | Leading device_id is unconstrained; planner may scan or use special strategies. |
device_id=? AND priority=? | Primarily device_id; priority has a gap before it. | occurred_at is not constrained. |
Prove prefix behavior with plans
DROP INDEX IF EXISTS idx_note_device_time;CREATE INDEX idx_note_dtpON maintenance_note(device_id, occurred_at, priority);-- A: leftmost equalityEXPLAIN QUERY PLANSELECT count(*) FROM maintenance_note WHERE device_id=42;-- B: equality plus rangeEXPLAIN QUERY PLANSELECT count(*) FROM maintenance_noteWHERE device_id=42 AND occurred_at >= '2026-06-01T00:00:00Z';-- C: later column without the leading keyEXPLAIN QUERY PLANSELECT count(*) FROM maintenance_noteWHERE priority=5;Do not memorize an exact plan string. Compare which WHERE terms appear in the SEARCH detail. Query C may scan the table or an index; it does not get a simple priority=? lookup from this key order.
Filtering plus ORDER BY: equality first, then order range
A frequent composite-index shape is equality columns followed by the columns that establish useful order. Here, constraining one device means SQLite can walk that device’s contiguous time range in descending order.
DROP INDEX IF EXISTS idx_note_dtp;CREATE INDEX idx_note_device_timeON maintenance_note(device_id, occurred_at DESC);EXPLAIN QUERY PLANSELECT occurred_at, status, summaryFROM maintenance_noteWHERE device_id = 42ORDER BY occurred_at DESCLIMIT 10;The rule is not “always put WHERE columns first.” Different predicates, ranges, collations, direction, and output requirements matter. The repeatable method is to write the real query and inspect its plan.
Covering indexes: answer from the index alone
A covering index contains every value needed by a particular query: the search/order keys plus the projected columns. SQLite can then avoid returning to the table B-tree for those rows. Covering is a relationship between one query and an index, not a permanent property of the index.
DROP INDEX IF EXISTS idx_note_device_time;CREATE INDEX idx_note_device_time_coverON maintenance_note(device_id, occurred_at DESC, status, summary);EXPLAIN QUERY PLANSELECT occurred_at, status, summaryFROM maintenance_noteWHERE device_id=42ORDER BY occurred_at DESCLIMIT 10;Look for USING COVERING INDEX idx_note_device_time_cover. If you add cost_cents to the SELECT list, the same index may still help filtering and ordering but cease to cover the query because that value must come from the table row.
Adding every output column to chase “covering” increases index size, cache use, WAL/journal work, and write maintenance. Cover only hot read paths after measurement.
Redundant prefixes: one composite index may subsume a shorter one
If you already maintain (device_id, occurred_at), a separate index on only (device_id) is often redundant because the longer index has the same leftmost key. SQLite’s own query-planning tutorial recommends avoiding two indexes where one is a strict prefix of the other, absent a measured reason for the shorter structure.
CREATE INDEX IF NOT EXISTS idx_note_deviceON maintenance_note(device_id);CREATE INDEX IF NOT EXISTS idx_note_device_timeON maintenance_note(device_id, occurred_at DESC);PRAGMA index_list('maintenance_note');EXPLAIN QUERY PLANSELECT count(*) FROM maintenance_note WHERE device_id=42;-- If your workload has no measured reason to retain the short prefix index:DROP INDEX idx_note_device;Do not remove indexes mechanically: uniqueness, collation, sort direction, partial predicates, index width, and workload-specific costs can make seemingly overlapping indexes serve different purposes. First prove that they are semantically redundant for the access patterns you own.
Why two single-column indexes are not the same as one composite index
For WHERE device_id=? AND occurred_at>=?, creating one index on device_id and another on occurred_at does not automatically give SQLite the same contiguous key range as (device_id, occurred_at). SQLite generally chooses one index per table loop, except specialized strategies such as OR-by-union. A well-ordered composite index can encode the conjunction directly.
DROP INDEX IF EXISTS idx_note_device_time_cover;DROP INDEX IF EXISTS idx_note_device_time;CREATE INDEX idx_note_device ON maintenance_note(device_id);CREATE INDEX idx_note_time ON maintenance_note(occurred_at);EXPLAIN QUERY PLANSELECT * FROM maintenance_noteWHERE device_id=42 AND occurred_at >= '2026-06-01T00:00:00Z';CREATE INDEX idx_note_device_timeON maintenance_note(device_id, occurred_at);EXPLAIN QUERY PLANSELECT * FROM maintenance_noteWHERE device_id=42 AND occurred_at >= '2026-06-01T00:00:00Z';Failure cases and diagnosis
| Observation | Likely explanation | Evidence-driven correction |
|---|---|---|
| Composite index exists but query scans. | Leading key not constrained, result is broad, stats/cost favor scan, or expression/collation differs. | Inspect EQP and exact predicates; do not force an index reflexively. |
| Query SEARCHes but also uses temp B-tree for ORDER BY. | Index order does not fully match requested order after filtering constraints. | Test a candidate ordering that matches the hot query. |
| Covering disappeared after adding one SELECT column. | New projection is not stored in the index. | Accept table lookup or measure a wider covering index. |
| Many indexes share the same left prefix. | Schema accumulated indexes query-by-query. | Map queries to indexes and remove truly redundant structures. |
| A single-column later-key query is slow. | Composite leading column blocks a direct range. | Create a separate index only if that access pattern is important, or wait for stats evidence before assuming. |
Reproducible composite-index lab
Reset the maintenance-note indexes, then answer four questions with EQP: which index supports one device, which avoids the time sort, which query is covering, and which query cannot directly search a later column. Keep screenshots out of your notes; save the SQL and relevant plan words so the experiment remains reproducible across CLI versions.
DROP INDEX IF EXISTS idx_note_device;DROP INDEX IF EXISTS idx_note_time;DROP INDEX IF EXISTS idx_note_device_time_cover;DROP INDEX IF EXISTS idx_note_dtp;DROP INDEX IF EXISTS idx_note_device_time;CREATE INDEX idx_note_device_timeON maintenance_note(device_id, occurred_at DESC);PRAGMA index_list('maintenance_note');Composite-index checkpoint
Explain the key ordering, not just the syntax.
- Why can (device_id, occurred_at) usually support WHERE device_id=?
- Why does WHERE occurred_at=? not get the same simple lookup from that index?
- How can one index remove a separate ORDER BY sort?
- What makes an index covering for one query?
- Why is an index on device_id often redundant once (device_id, occurred_at) exists?
- Why is “one index per WHERE column” usually a weak design rule?
Review the answers
The longer index begins with device_id, so that leftmost range is searchable. A later column without the leading key is not the same contiguous lookup. If rows inside the equality prefix are already in the requested order, SQLite may avoid a temporary sort. Covering means every needed output/search value is in the index. A strict prefix index often duplicates capability while adding maintenance. Composite ordering should be derived from the whole access pattern, not one column at a time.
Production judgment and bridge
Composite indexing is a workload design exercise. Keep the query, candidate DDL, before/after EQP, result cardinality, and write/storage cost together in review. Lesson 3 adds two more precise tools: partial indexes that omit irrelevant rows and expression indexes that precompute an exact deterministic expression for planner matching.