SQLite Composite Indexes: Column Order and the Leftmost Prefix Rule

One Index, Several Columns

A composite index — sometimes called a multi-column index — is a single index built over two or more columns. You create one by listing the columns in order:

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    status TEXT NOT NULL,
    created_at TEXT NOT NULL,
    total REAL NOT NULL
);

CREATE INDEX idx_orders_customer_status
    ON orders(customer_id, status);

INSERT INTO orders(customer_id, status, created_at, total) VALUES
    (1, 'paid',    '2026-01-10', 49.00),
    (1, 'paid',    '2026-02-03', 12.50),
    (1, 'pending', '2026-02-14',  9.99),
    (2, 'paid',    '2026-01-22', 80.00),
    (2, 'refunded','2026-02-01', 25.00);

SELECT * FROM orders WHERE customer_id = 1 AND status = 'paid';

The index idx_orders_customer_status stores entries sorted first by customer_id, then by status within each customer. That ordering is the whole story — everything else about composite indexes follows from it.

The Mental Model: A Sorted Phone Book

Picture an old phone book. Entries are sorted by last name, and within each last name, by first name. That's exactly what an index on (last_name, first_name) looks like.

Some lookups are cheap, others aren't:

• "Find everyone named Patel" — easy, all the Patels sit together.
• "Find Priya Patel" — easy, jump to Patel, then scan to Priya.
• "Find everyone named Priya" — slow, you have to scan every page. Priyas are scattered across every last name.

A composite SQLite index works the same way. The first column is the primary sort key; the second column only sorts entries that share the same first-column value.

The Leftmost Prefix Rule

SQLite can use a composite index for a query only when the WHERE clause constrains a leftmost prefix of its columns. For an index on (a, b, c):

• Filtering on a — uses the index.
• Filtering on a and b — uses the index.
• Filtering on a, b, and c — uses the index.
• Filtering on b alone, or c alone, or b and c — index is not used.

You can verify this directly with EXPLAIN QUERY PLAN:

CREATE TABLE events (
    id INTEGER PRIMARY KEY,
    user_id INTEGER NOT NULL,
    kind TEXT NOT NULL,
    occurred_at TEXT NOT NULL
);

CREATE INDEX idx_events_user_kind_time
    ON events(user_id, kind, occurred_at);

EXPLAIN QUERY PLAN
SELECT * FROM events WHERE user_id = 42 AND kind = 'login';

EXPLAIN QUERY PLAN
SELECT * FROM events WHERE kind = 'login';

The first plan reports SEARCH events USING INDEX idx_events_user_kind_time. The second falls back to SCAN events — filtering on kind alone skips the leading user_id column, so the index is useless for that query.

Column Order Is a Design Decision

Because the leftmost prefix matters, the order you list columns in CREATE INDEX is a real choice, not a stylistic one. Two rules of thumb:

1. Put the column you filter on most often first. That column unlocks the index for the widest range of queries.
2. Put equality columns before range columns. SQLite can drill into the index using =, then scan a contiguous range using <, >, or BETWEEN — but only on the last used column.

CREATE TABLE sales (
    id INTEGER PRIMARY KEY,
    region TEXT NOT NULL,
    sold_at TEXT NOT NULL,
    amount REAL NOT NULL
);

-- Good: equality on region first, range on sold_at second.
CREATE INDEX idx_sales_region_time ON sales(region, sold_at);

INSERT INTO sales(region, sold_at, amount) VALUES
    ('EU', '2026-01-05', 120),
    ('EU', '2026-02-11', 80),
    ('US', '2026-01-09', 200),
    ('US', '2026-02-20', 150);

EXPLAIN QUERY PLAN
SELECT * FROM sales
WHERE region = 'EU' AND sold_at >= '2026-02-01';

The plan shows SEARCH sales USING INDEX idx_sales_region_time (region=? AND sold_at>?). SQLite jumps straight to region = 'EU', then walks forward through the date range. Flip the column order to (sold_at, region) and the same query has to scan every row in the date range and recheck region for each.

Composite vs. Several Single-Column Indexes

A common question: should I create one index on (a, b), or two separate indexes on a and b?

CREATE TABLE tickets (
    id INTEGER PRIMARY KEY,
    project_id INTEGER NOT NULL,
    assignee_id INTEGER NOT NULL,
    state TEXT NOT NULL
);

-- Option A: one composite index.
CREATE INDEX idx_tickets_proj_state ON tickets(project_id, state);

-- Option B (alternative): two single-column indexes.
-- CREATE INDEX idx_tickets_project ON tickets(project_id);
-- CREATE INDEX idx_tickets_state   ON tickets(state);

EXPLAIN QUERY PLAN
SELECT * FROM tickets
WHERE project_id = 7 AND state = 'open';

For the combined filter, the composite index is faster — SQLite walks straight to the matching (project_id, state) entries. With two single-column indexes, SQLite typically picks one, uses it to narrow rows, then rechecks the other column on each matching row. It can sometimes intersect them, but the composite index is the cleaner answer when the columns are queried together.

If project_id and state are also queried independently, you may want both — the composite for the combined filter, plus a single-column index on state for queries that filter only on it.

Covering Indexes

When an index includes every column a query needs — both filter columns and selected columns — SQLite can answer the query without touching the table at all. That's a covering index, and it's the fastest a query gets.

CREATE TABLE invoices (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    issued_at TEXT NOT NULL,
    total REAL NOT NULL,
    notes TEXT
);

CREATE INDEX idx_invoices_cover
    ON invoices(customer_id, issued_at, total);

INSERT INTO invoices(customer_id, issued_at, total, notes) VALUES
    (1, '2026-01-12', 100, 'first'),
    (1, '2026-02-02', 250, NULL),
    (2, '2026-01-30',  75, 'rush');

EXPLAIN QUERY PLAN
SELECT issued_at, total FROM invoices WHERE customer_id = 1;

The plan shows USING COVERING INDEX idx_invoices_cover. The query reads issued_at and total straight out of the index — notes and id aren't needed, so the table itself is never opened. Adding a column to a composite index purely to cover a hot query is a worthwhile trade when that query runs constantly.

Composite UNIQUE Constraints

Composite indexes also enforce uniqueness across column combinations. Useful when no single column is unique on its own, but the combination must be:

CREATE TABLE enrollments (
    student_id INTEGER NOT NULL,
    course_id  INTEGER NOT NULL,
    enrolled_at TEXT NOT NULL
);

CREATE UNIQUE INDEX idx_enrollments_pair
    ON enrollments(student_id, course_id);

INSERT INTO enrollments VALUES (1, 100, '2026-01-05');
INSERT INTO enrollments VALUES (1, 101, '2026-01-05');  -- ok, different course
INSERT INTO enrollments VALUES (1, 100, '2026-02-01');  -- fails: pair repeats

The third insert raises UNIQUE constraint failed: enrollments.student_id, enrollments.course_id. The same pair already exists in the index, so SQLite refuses the duplicate.

Pitfalls Worth Knowing

• OR between non-leading columns blocks the index. WHERE a = 1 OR b = 2 on an index (a, b) typically can't use the index at all — SQLite needs to consider both branches separately.
• Functions on indexed columns disable the index. WHERE lower(email) = 'x' won't use an index on email. Index the expression instead, or normalise the data on insert.
• Indexes aren't free. Every index is updated on every INSERT, UPDATE (of indexed columns), and DELETE. Three composite indexes on a write-heavy table can dominate write cost.
• Run ANALYZE after building indexes. SQLite's planner uses statistics gathered by ANALYZE to choose between candidate indexes. Without those stats, it falls back on heuristics that aren't always optimal.

A Practical Workflow

When tuning a slow query, the loop usually looks like this:

1. Run EXPLAIN QUERY PLAN on the query to see what SQLite is doing today.
2. If it's scanning, look at the WHERE clause — what's the equality column? What's the range column? What's selected?
3. Build a composite index ordered equality-first, range-second, with selected columns appended if covering helps.
4. Run ANALYZE.
5. Run EXPLAIN QUERY PLAN again. Confirm the plan changed and the index is being used.
6. Time the query before and after on representative data.

Skip step 6 at your peril. An index that looks correct in the plan can still be slower in practice if the table is small or the planner picks a different path.

Next: Partial Indexes

Composite indexes cover every row in the table. But often, only a small subset of rows matters — open tickets, unprocessed jobs, non-deleted records. A partial index lets you index just those rows, with a WHERE clause baked into the index itself. That's the next page.