Composite Indexes in SQL: Leftmost-Prefix Rule and Column Order

A composite index covers several columns in one structure. The right column order turns filtering, sorting and even fetching the needed columns into a single pass over the index; the wrong order makes the index nearly useless.

The leftmost-prefix rule

A B-tree sorts rows by columns left to right, like words in a dictionary. So the index is usable only starting from its leftmost column, with no gaps.

CREATE INDEX idx_orders_user_created
  ON orders (user_id, created_at DESC);

• WHERE user_id = 42 works, it is the leftmost prefix.
• WHERE user_id = 42 AND created_at > now() - interval '7 days' uses both columns.
• WHERE created_at > now() - interval '7 days' cannot use this index: the second column is unreachable without the first.

Gotcha: equality on the first column "unlocks" sorting and ranges on the second. But a range on the first column kills point lookups on the second, because after > or < the rows are no longer ordered by the next column.

Column order: filter, then sort, then range

A practical rule: equality columns first, the sort column next, the range column last.

-- "a user's recent orders, newest first"
SELECT id, amount, status
FROM orders
WHERE user_id = 42
  AND created_at >= '2026-01-01'
ORDER BY created_at DESC
LIMIT 20;

The index (user_id, created_at DESC) is ideal: user_id narrows by equality, and created_at DESC returns rows already ordered, so no separate sort step is needed.

• Equality (=, IN) goes first.
• The ORDER BY column comes right after equalities.
• The range (>, <, BETWEEN) goes last, because it breaks ordering for anything further in the index.

Covering an ORDER BY and sort direction

For the index to remove the sort step, the ORDER BY direction must match the index or be its full reverse. PostgreSQL can scan a B-tree in both directions.

CREATE INDEX idx_emp_dept_salary
  ON employees (dept, salary DESC);

SELECT name, salary
FROM employees
WHERE dept = 'eng'
ORDER BY salary DESC
LIMIT 10;

ORDER BY salary ASC also works without a sort, that is a backward scan. But a mixed ORDER BY dept ASC, salary ASC against an index (dept, salary DESC) forces a re-sort: per-column directions cannot be mixed freely.

INCLUDE columns and index-only scans

If the index holds every column the query needs, the engine never touches the table, an index-only scan. Columns you do not filter on are best added via INCLUDE: they live only in the leaf pages and do not bloat tree navigation.

CREATE INDEX idx_orders_user_inc
  ON orders (user_id, created_at DESC)
  INCLUDE (amount, status);

SELECT created_at, amount, status
FROM orders
WHERE user_id = 42
ORDER BY created_at DESC;

• In PostgreSQL an index-only scan needs a recently vacuumed table (the visibility map).
• MySQL/InnoDB has no INCLUDE keyword, but the primary key is auto-appended to every secondary index, and a "covering index" is built by simply listing every column in the index itself.
• ClickHouse is different: its primary index is sparse and non-unique, and the table's ORDER BY defines the on-disk data ordering.

When a composite beats two single-column indexes

A planner can combine two separate indexes on user_id and created_at via a bitmap, but that is two scans plus an intersection, slower than one index that already stores the data in order.

-- bitmap combination of two indexes: works, but yields no ordering
CREATE INDEX idx_orders_user ON orders (user_id);
CREATE INDEX idx_orders_created ON orders (created_at);

• One composite index wins when the columns are consistently queried together in WHERE/ORDER BY.
• Two single indexes are more flexible when columns are also queried independently.
• Do not over-index: every index slows INSERT/UPDATE and costs space. (a, b) already serves queries on a, so a separate index on a is usually redundant.