SQLMentor // articles

Composite Index Column Order: Why It Matters

A composite index on (a, b) is not interchangeable with one on (b, a) — column order determines exactly which queries the index can actually serve. Here's the leftmost-prefix rule and the two heuristics for getting the order right.

The leftmost-prefix rule

A composite (multi-column) index is only efficiently usable starting from its leftmost column. An index on (department_id, hire_date) can serve a query filtering on department_id alone, or on both columns together — but a query filtering on hire_date alone generally can't use it efficiently, because the index isn't independently sorted by hire_date.

This single rule is why column order in a composite index is a real design decision, not a cosmetic one — the same two columns in a different order serve a different set of queries.

The same columns, two different indexes

Neither index is "wrong" — they serve different query patterns.

CREATE INDEX idx_a ON employees (department_id, hire_date);
-- Serves: WHERE department_id = ?
-- Serves: WHERE department_id = ? AND hire_date > ?
-- Does NOT efficiently serve: WHERE hire_date > ?  (alone)

CREATE INDEX idx_b ON employees (hire_date, department_id);
-- Serves: WHERE hire_date > ?
-- Serves: WHERE hire_date > ? AND department_id = ?
-- Does NOT efficiently serve: WHERE department_id = ?  (alone)

Two rules of thumb for ordering columns

RuleWhy
Equality columns before range columnsAfter a range condition (>, <, BETWEEN), the index can't use later columns to narrow the search further — put columns compared with = first, ranged columns last
More selective / more frequently filtered-alone columns firstThe leading column determines which single-column queries the index can serve at all — put the column most often queried by itself, or that narrows the result the most, in that position

A bonus: matching ORDER BY

If a query filters on the leading column(s) with equality and then sorts by the next column, the same composite index can often satisfy the sort too — avoiding a separate sort step in the execution plan. WHERE department_id = 60 ORDER BY hire_date can be served entirely by the (department_id, hire_date) index above: Oracle finds the matching rows and they're already in hire_date order within that department.

Frequently asked questions

Can a composite index serve a query that only filters on the second column?
Not efficiently in general. Because the index is physically sorted by the leading column first, values of the second column are scattered throughout the index rather than grouped together — the database usually falls back to a full index scan or table scan instead of an efficient range lookup.
Should equality columns always come before range columns?
As a strong default, yes — it's the single most reliable ordering heuristic. Once a range condition appears in the index key, the database can't use subsequent columns to narrow the search within that range as precisely, so ranged columns belong last.
How many columns should a composite index have?
As few as actually serve real query patterns. Every additional column adds index size and write overhead; adding columns "just in case" without a query that benefits from them is pure cost with no upside. Look at actual WHERE/ORDER BY patterns before adding a column.
Does column order matter for a unique constraint the same way?
The uniqueness check itself considers all columns together regardless of order, but the underlying index Oracle creates to enforce a composite unique constraint follows the same leftmost-prefix rules for query performance — so it's worth choosing an order that also serves common queries, not just the constraint.

Keep learning