Queries on an orders table filter with status = 'ACTIVE' and created_at > (some timestamp). Should the multi-column index key be (status, created_at) or (created_at, status), and what general rule about equality and range predicates does your choice follow?

middlemust knowfreq 70%

answer

Equality first, range last
Range ends the chain
Only one seekable range column
IN-list ≈ several equality seeks
Right order = seek then walk, no discards

basics

~20 s

Use (status, created_at). Equality columns come first, the range column last. A range predicate on a leading column scatters the columns after it, so only one range column can be used for the seek and it must be the final key column used.

solid answer

~50 s

Choose (status, created_at). The rule is equality columns first, then the range column.

Why: index entries sort by the key left to right. With (status, created_at), all ACTIVE rows form one contiguous block, and inside that block rows are sorted by created_at, so the engine descends once to ('ACTIVE', :t) and walks forward until status changes. Every entry it touches is a result.

With (created_at, status), the seek starts at :t and walks every row created since then, regardless of status; status becomes a filter applied per entry, not a seek boundary. If only 2% of recent rows are ACTIVE, you read 50x more index entries than you return.

The general form: a range predicate ends the useful part of the key. Everything after it can only filter, not narrow. So put all equality-constrained columns first (their relative order among themselves is mostly free), then the single most useful range column, then any column present only for sorting or payload.

Choose `(status, created_at)`. The rule is **equality columns first, then the range column**. Why: index entries sort by the key left to right. With `(status, created_at)`, all `ACTIVE` rows form one contiguous block, and *inside* that block rows are sorted by `created_at`, so the engine descends once to `('ACTIVE', :t)` and walks forward until status changes. Every entry it touches is a result. With `(created_at, status)`, the seek starts at `:t` and walks every row created since then, regardless of status; `status` becomes a filter applied per entry, not a seek boundary. If only 2% of recent rows are ACTIVE, you read 50x more index entries than you return. The general form: a range predicate ends the useful part of the key. Everything after it can only filter, not narrow. So put all equality-constrained columns first (their relative order among themselves is mostly free), then the single most useful range column, then any column present only for sorting or payload.

code

sql · 13 lines

sql

-- workload
SELECT id, customer_id
FROM   orders
WHERE  status = 'ACTIVE'
  AND  created_at > :t
ORDER BY created_at DESC
LIMIT 20;

-- good: equality column first, range column last
CREATE INDEX idx_orders_status_created ON orders (status, created_at);

-- bad: range first, equality degraded to a filter
CREATE INDEX idx_orders_created_status ON orders (created_at, status);

go deeper

for a junior

Give the choice and the rule: equality columns first, the range column last, because a range spreads out everything after it.

for a middle

Explain the mechanism with the contiguity argument and quantify the difference — entries read equals rows returned in the good order, versus everything newer than the cutoff in the bad one.

for a senior

Add operational judgment: only one seekable range per index, IN-lists behave like equality seeks, ORDER BY plus LIMIT amplifies the penalty, and the equality prefix order should be chosen so its sub-prefixes serve other query shapes.

for a principal

Discuss it as index-set design across a workload — which equality prefix to standardize on (tenant, then status), when a partial index beats a wider key, and the write-cost budget that limits how many such keys you keep.

The two candidate keys

Workload: WHERE status = 'ACTIVE' AND created_at > :t. One equality predicate, one range predicate, two possible key orders.

Key (status, created_at) — entries sort by status; within 'ACTIVE', by timestamp. The matching rows form a single contiguous run starting at ('ACTIVE', :t) and ending where status stops being 'ACTIVE'. The engine descends the tree once, then walks that run. Entries read ≈ rows returned.

Key (created_at, status) — entries sort by timestamp; within one timestamp, by status. Rows with created_at > :t are contiguous, but ACTIVE ones are interleaved with every other status inside that run. The engine descends to :t, walks the entire tail of the index, and discards non-ACTIVE entries one by one. Entries read ≈ all rows newer than :t.

The underlying rule

A B+Tree seek walks a contiguous range of the sort order. Equality on a key column collapses that column to a single value, so the next column remains sorted within the collapsed block — the chain of usefulness continues. A range on a key column selects many values of that column, so the next column restarts its ordering inside every one of those values, and is no longer globally sorted within the range being scanned.

Hence: each key column stays useful for narrowing only while all columns to its left are pinned by equality. The first range predicate is where narrowing stops. Everything after it is a residual filter (it reduces returned rows and heap lookups, but not index entries scanned).

A practical corollary: only one range column per index can be used for seeking. An index on (a, b) for WHERE a > 1 AND b > 2 seeks on a > 1 only.

Ordering among the equality columns

Once the equality columns are grouped at the front, their relative order does not change how many entries are scanned for a query that pins all of them — any permutation isolates the same block. It matters for other reasons:

Prefix reuse. (tenant_id, status, created_at) also serves WHERE tenant_id = ? alone; (status, tenant_id, created_at) serves WHERE status = ? alone instead. Pick the order that makes the useful sub-prefixes match your other query shapes.
Compression and locality. Repeating a low-cardinality leading value can compress better and keeps related rows physically adjacent in the index.
The old folklore "most selective column first" is not a rule for this case; a column that no query filters on alone gains nothing from being leftmost.

The IN-list nuance

IN (…) is not a true range — many engines treat it as a set of equality seeks, doing one descent per list value and preserving the ability to seek on later key columns. So WHERE status IN ('A','B') AND created_at > :t can still use (status, created_at) well. BETWEEN, >, <, and prefix LIKE 'abc%' are genuine ranges and do end the chain.

Sorting interacts too

If the same query also does ORDER BY created_at DESC LIMIT 20, (status, created_at) is doubly right: after seeking to the ACTIVE block, the entries are already ordered by created_at, so the engine can walk backwards and stop after 20 rows with no sort step. (created_at, status) gives the sort order but forces scanning until 20 ACTIVE rows are found — unbounded work when ACTIVE is rare.

How to answer in an interview

Lead with the choice and the rule in one sentence, then quantify: with the wrong order you scan every row newer than :t; with the right one you scan only the ACTIVE ones in that window. Add "only one range column can drive the seek" and the IN-list nuance if there is room.

## The two candidate keys Workload: `WHERE status = 'ACTIVE' AND created_at > :t`. One equality predicate, one range predicate, two possible key orders. **Key `(status, created_at)`** — entries sort by status; within `'ACTIVE'`, by timestamp. The matching rows form a single contiguous run starting at `('ACTIVE', :t)` and ending where status stops being `'ACTIVE'`. The engine descends the tree once, then walks that run. Entries read ≈ rows returned. **Key `(created_at, status)`** — entries sort by timestamp; within one timestamp, by status. Rows with `created_at > :t` are contiguous, but ACTIVE ones are interleaved with every other status inside that run. The engine descends to `:t`, walks the entire tail of the index, and discards non-ACTIVE entries one by one. Entries read ≈ all rows newer than `:t`. ## The underlying rule A B+Tree seek walks a contiguous range of the sort order. Equality on a key column collapses that column to a single value, so the *next* column remains sorted within the collapsed block — the chain of usefulness continues. A range on a key column selects many values of that column, so the next column restarts its ordering inside every one of those values, and is no longer globally sorted within the range being scanned. Hence: **each key column stays useful for narrowing only while all columns to its left are pinned by equality.** The first range predicate is where narrowing stops. Everything after it is a residual filter (it reduces returned rows and heap lookups, but not index entries scanned). A practical corollary: **only one range column per index can be used for seeking.** An index on `(a, b)` for `WHERE a > 1 AND b > 2` seeks on `a > 1` only. ## Ordering among the equality columns Once the equality columns are grouped at the front, their relative order does not change how many entries are scanned for a query that pins all of them — any permutation isolates the same block. It matters for other reasons: - **Prefix reuse.** `(tenant_id, status, created_at)` also serves `WHERE tenant_id = ?` alone; `(status, tenant_id, created_at)` serves `WHERE status = ?` alone instead. Pick the order that makes the useful sub-prefixes match your other query shapes. - **Compression and locality.** Repeating a low-cardinality leading value can compress better and keeps related rows physically adjacent in the index. - The old folklore "most selective column first" is not a rule for this case; a column that no query filters on alone gains nothing from being leftmost. ## The IN-list nuance `IN (…)` is not a true range — many engines treat it as a set of equality seeks, doing one descent per list value and preserving the ability to seek on later key columns. So `WHERE status IN ('A','B') AND created_at > :t` can still use `(status, created_at)` well. `BETWEEN`, `>`, `<`, and prefix `LIKE 'abc%'` are genuine ranges and do end the chain. ## Sorting interacts too If the same query also does `ORDER BY created_at DESC LIMIT 20`, `(status, created_at)` is doubly right: after seeking to the ACTIVE block, the entries are already ordered by `created_at`, so the engine can walk backwards and stop after 20 rows with no sort step. `(created_at, status)` gives the sort order but forces scanning until 20 ACTIVE rows are found — unbounded work when ACTIVE is rare. ## How to answer in an interview Lead with the choice and the rule in one sentence, then quantify: with the wrong order you scan every row newer than `:t`; with the right one you scan only the ACTIVE ones in that window. Add "only one range column can drive the seek" and the `IN`-list nuance if there is room.

The query adds a second range predicate: amount > 100. Where does amount belong in the key?
Only one range column can drive the seek, so a second range column cannot narrow the scan no matter where you put it. Keep the more selective range as the last seekable column and treat the other as a residual filter — placing it in the index still avoids a table lookup for rows it eliminates, which is worth something, but it does not reduce index entries scanned. If both ranges are individually weak, the real answer may be a different index or a partial index.
Does 'most selective column first' contradict the equality-first rule?
It is a weaker heuristic that only applies among columns of the same predicate kind, and even then it mainly affects other query shapes rather than this one. Once all equality columns are pinned, any permutation of them isolates the same block of entries, so selectivity ordering does not change the scan size. The rule that genuinely changes cost is equality before range.
Does an IN list behave like an equality or like a range for key ordering?
Mainstream engines expand a short IN list into multiple equality seeks — one descent per value — so the columns after it remain seekable, unlike a true range. Very long IN lists can tip the planner into a scan instead, and the multi-seek plan loses the single-pass ordering property, so an ORDER BY on a later key column may still require a sort or a merge of the per-value runs.

A filing cabinet with drawers labelled by status and folders inside each drawer ordered by date. Open the ACTIVE drawer and flip to the date — you touch only what you want. Order it the other way and you flip through every folder since that date, checking a status sticker on each.

saying these in an interview costs you the question

Putting the timestamp or other range column first because 'it is the most selective'
Believing a second range column in the key adds a second seek boundary
Claiming column order in a composite index is only about ORDER BY
Assuming a residual filter after the range column reduces index pages read
Treating 'most selective column first' as an absolute rule that overrides equality-before-range

Queries on an orders table filter with status = 'ACTIVE' and created_at > (some timestamp). Should the multi-column index key be (status, created_at) or (created_at, status), and what general rule about equality and range predicates does your choice follow?

§ answer

basics

solid answer

code

§ go deeper

▾level notes4 levels

▾deep dive~3 min

The two candidate keys

The underlying rule

Ordering among the equality columns

The IN-list nuance

Sorting interacts too

How to answer in an interview

▾common follow-ups3 questions

▾analogy~20 s

▾red flags5 flags

§ context

▾related questions5 questions

▾related topics3 topics

The two candidate keys

The underlying rule

Ordering among the equality columns

The IN-list nuance

Sorting interacts too

How to answer in an interview

answer

go deeper

context