Explain Composite Indexes & Leftmost Prefix in Databases

2 / 66

indexing

Composite Indexes & Leftmost Prefix

A single index on multiple columns can serve many queries — but only if the WHERE clause uses the columns from the left.

A composite index covers several columns in one structure. One well-ordered composite index can replace three single-column ones.

A phone book sorted by last name, first name, middle initial. Useful for "Smith, John" — not for "anyone named John".

Key Concepts

The catch is that column order decides which queries it helps, and this is the most-tested fact about indexes.

Why order matters — look at the sort. An index on (country, city, name) sorts by country first, then city within each country, then name within each city:

(country, city, name)

    IN  Bengaluru  Asha
    IN  Bengaluru  Ravi
    IN  Chennai    Meera
    UK  London     Asha
    UK  London     Zoe
    US  Austin     Meera

Now read that list and answer two questions.

"Everyone in India?" The IN rows sit together at the top. One jump, then read forward.

"Everyone named Asha?" Asha appears on line 1 and line 4, with unrelated rows between. There is no block to jump to. You must read every line.

That is the leftmost prefix rule, and the sorted list is the whole explanation. The index helps when you filter on country, or country + city, or all three. It does not help city alone, name alone, or city + name without country.

countrycountry + citycitynamecity + name

A range stops the index at that column. Consider (status, created_at, priority) with:

(status, created_at, priority)

    WHERE status = 'OPEN' AND created_at > '2026-01-01' AND priority = 1

Equality on status picks one block. The range on created_at then spans many values — and within that span, priority is not in any useful order, because the sort restarts for every distinct created_at.

statuscreated_atpriority

So the index seeks on status and created_at, then checks priority row by row. Equality columns first, range columns last.

statuscreated_atpriority

How to order the columns.

  1. Columns used with `=` come first
  2. Among those, the most selective first — the one that eliminates the most rows
  3. Range conditions (`>`, `<`, `BETWEEN`) last
  4. If a query ends with `ORDER BY x`, putting `x` next can remove the sort step

=><BETWEENORDER BY x

The redundancy worth spotting. An index on (a, b) already serves every query that an index on a alone would serve, because a is a leftmost prefix of it. Keeping both is paying twice on every write for nothing. A separate index on b is not redundant — that one you may genuinely need.

(a, b)ab

What the interviewer is probing.1. "What is the leftmost prefix rule?" Probing: the central idea. Stalls: "Column order does not matter." Moves up: an index on (a, b, c) serves predicates on a, on a and b, or all three — but not on b alone, because the sort order starts with a.

2. "How do you order columns in a composite index?" Probing: the heuristic. Stalls: "Alphabetically." Moves up: equality predicates first, then the range or sort column — a range stops the index being usable for columns after it.

3. "Do you still need a single-column index on the first column?" Probing: the redundancy. Stalls: "Yes." Moves up: no — the composite already serves it, so the separate index is redundant write cost.

4. "Why does a range predicate limit the rest of the index?" Probing: the mechanism. Stalls: "It does not." Moves up: rows are ordered by later columns only within equal values of earlier ones, so after a range the later columns are no longer sorted usefully.