SQLite indexes: the leftmost prefix is a consequence of sorting

Everyone has memorized that a composite index (a, b) serves WHERE a = ? and WHERE a = ? AND b = ? but not WHERE b = ?. The real reason is simple: an index is rows in a sorted order, and that order decides what you can look up.

An index is a sort, not a set

CREATE INDEX idx ON t (a, b) produces this order: sort by a, then by b within equal a. On disk it looks like:

a=1, b=1
a=1, b=5
a=1, b=9
a=2, b=3
a=2, b=7
a=3, b=2

Look up a = 2: one contiguous run, binary search. Look up a = 2 AND b = 7: find the a = 2 run, which is ordered by b, then binary search again.

Look up b = 7: the value is scattered across every run. No contiguous interval holds all matches. A full scan is the only option.

That is not a database convention. It is what sorting means.

Where equality ends and range begins

Inside a composite index, the first range condition stops later columns from being used:

-- uses (a, b)
WHERE a = 1 AND b = 2

-- a is used; b is only ordered, not locatable
WHERE a = 1 AND b > 2

-- b cannot narrow anything
WHERE a > 1 AND b = 2

The third form is the easy mistake. a > 1 matches many runs, and b is ordered independently inside each of them.

Choosing column order

Rule	Why
Equality first, range after	a range truncates later columns
Higher selectivity first	discard more rows sooner
Append covered columns last	avoid a table lookup

The third is the most valuable use of a composite index:

CREATE INDEX idx_user_time ON posts (user_id, created_at, title);

SELECT created_at, title FROM posts WHERE user_id = ? ORDER BY created_at DESC can be answered from the index alone. Every extra column widens the index; if it grows to hold the whole row, just read the table.

Verify with EXPLAIN, do not guess

EXPLAIN QUERY PLAN
SELECT * FROM posts WHERE user_id = 7 ORDER BY created_at DESC;

SEARCH posts USING INDEX idx_user_time means the index is in play; SCAN means a full table scan. In SQLite, ANALYZE refreshes statistics so the planner picks the right index.

Index order is sort order. Decide what order you fetch in, and the column order falls out.