Does an index on (a, b) help a query filtering only on b?

The Answer

Say this in the room. 45 seconds.

• No. An index on (a, b) is sorted by a first, then by b within each a. There is no place where all the rows with a given b sit together.
• So it serves WHERE a = ? and WHERE a = ? AND b = ?, and does nothing for WHERE b = ?. That is the leftmost prefix rule.
• The order of the columns is therefore the whole decision. (a, b) and (b, a) are different indexes with different capabilities, not two spellings of one.
• Practical order: equality columns first, then the column used for a range or an ORDER BY. Only one range column can be used, and everything after it is dead weight for filtering.
• An index on (a) is redundant beside one on (a, b). An index on (b) is not.
• Add the selected columns to the end and the row is never fetched at all — a covering index.

Understand It

As in the previous entry, the runnable blocks here are a model, not a database. Three indexes over the same million rows, each sorted by its columns in order — which is the one property that produces every behaviour below.

Sorted by the first column, then the second

Think about what (customer_id, status) physically looks like: every entry for customer 1, ordered by status; then every entry for customer 2, ordered by status; and so on. Ask for a customer and you descend straight to their block. Ask for a status and there is nowhere to descend to — every block contains some.

int customer = 12_345, open = 1;

int[] byCustomer = seek(CUSTOMER_STATUS, customer, Verify::lead);
int cursor = byCustomer[0], matched = 0;
while (cursor < ROWS && lead(CUSTOMER_STATUS[cursor]) == customer) { cursor++; matched++; }

System.out.println("  index on (customer_id, status)");
System.out.println("    WHERE customer_id = ? : " + byCustomer[1] + " comparisons, " + matched + " entries");

int examined = 0, matchedStatus = 0;
for (int i = 0; i < ROWS; i++) {
    examined++;
    if (second(CUSTOMER_STATUS[i]) == open) matchedStatus++;
}
System.out.println("    WHERE status = ?      : " + examined + " entries examined, " + matchedStatus + " matched");

  index on (customer_id, status)
    WHERE customer_id = ? : 20 comparisons, 4 entries
    WHERE status = ?      : 1000000 entries examined, 250000 matched

Twenty comparisons for the leading column. For the second column alone, every entry in the index had to be examined — a million of them, to find the quarter that matched.

A real database can do this too, and calls it an index full scan. It is occasionally chosen when the index is much narrower than the table, so it is not literally useless — but it is linear, and it is not what the person who created the index expected. In plan output it appears as a full index scan rather than an index seek, and confusing the two is how "but I added an index" survives a review.

The rule generalises to any number of columns. An index on (a, b, c) serves:

You may skip columns from the right, never from the left.

Column order decides what else you get free

Because the second column is sorted within each value of the first, (customer_id, status) gives an ordered read of statuses for one customer. Reverse the columns and it cannot:

int customer = 12_345;

int[] good = seek(CUSTOMER_STATUS, customer, Verify::lead);
List<Long> inOrder = new ArrayList<>();
int cursor = good[0];
while (cursor < ROWS && lead(CUSTOMER_STATUS[cursor]) == customer) {
    inOrder.add(second(CUSTOMER_STATUS[cursor]));
    cursor++;
}

System.out.println("  WHERE customer_id = ? ORDER BY status");
System.out.println("    (customer_id, status) : " + good[1] + " comparisons, read "
    + inOrder.size() + " in order, sorted? " + inOrder.equals(inOrder.stream().sorted().toList()));

int scanned = 0, found = 0;
for (int i = 0; i < ROWS; i++) {
    scanned++;
    if (second(STATUS_CUSTOMER[i]) == customer) found++;
}
System.out.println("    (status, customer_id) : " + scanned + " entries scanned to find "
    + found + " — no seek possible");

  WHERE customer_id = ? ORDER BY status
    (customer_id, status) : 20 comparisons, read 4 in order, sorted? true
    (status, customer_id) : 1000000 entries scanned to find 4 — no seek possible

Same two columns, fifty thousand times the work. The first index descends to the customer in twenty comparisons and reads four entries that are already in status order. The second cannot be entered on customer_id at all, so every entry has to be examined to find the same four.

Worth being precise about one thing: the reversed index does return its entries in status order — but only because reading the whole of it happens to walk statuses in order. It paid a million reads for an ordering the first index got for free after twenty comparisons. Order is not the difference here; the seek is.

Which gives the ordering rule worth memorising:

1. Equality columns first — every column you compare with =.
2. Then the range or ORDER BY column — the one used with >, BETWEEN, or ordering.
3. Stop. Only one range column can be used for seeking; columns after it can still cover, but they cannot narrow the scan.

That third point is the subtle one. In WHERE a = ? AND b > ? AND c = ? with an index on (a, b, c), the c predicate cannot restrict the range — once b is a range, the entries are no longer grouped by c. Reorder to (a, c, b) and all three are used.

Selectivity — putting the most distinctive column first — is the advice people repeat, and it is secondary. An index that is very selective on a column nobody filters by is worth nothing. Usability first, selectivity second.

Widen it and the table is never touched

The columns after the ones you filter on still earn their place, because they can carry the answer:

int customer = 12_345;

tableFetches = 0;
long viaLookup = 0;
int a = seek(CUSTOMER_STATUS, customer, Verify::lead)[0];
while (a < ROWS && lead(CUSTOMER_STATUS[a]) == customer) {
    viaLookup += fetchTotal(rowOf(CUSTOMER_STATUS[a]));
    a++;
}
int fetchesA = tableFetches;

tableFetches = 0;
long viaCovering = 0;
int b = seek(COVERING, customer, Verify::covLead)[0];
while (b < ROWS && covLead(COVERING[b]) == customer) {
    viaCovering += covTotal(COVERING[b]);
    b++;
}
int fetchesB = tableFetches;

System.out.println("  SELECT total WHERE customer_id = ?");
System.out.println("    (customer_id, status)        : total " + viaLookup + ", row fetches " + fetchesA);
System.out.println("    (customer_id, status, total) : total " + viaCovering + ", row fetches " + fetchesB);
System.out.println("    same answer? " + (viaLookup == viaCovering));

  SELECT total WHERE customer_id = ?
    (customer_id, status)        : total 1060, row fetches 4
    (customer_id, status, total) : total 1060, row fetches 0
    same answer? true

Same answer, and the wider index never read a row. Four fetches is nothing here; at four hundred matching rows in a table that does not fit in memory, four hundred random reads is the query.

This is why the right response to a slow indexed query is often to widen an existing index rather than add a new one. A wider index costs the same single write per insert as a narrow one — the write cost is per index, not per column — while removing a random read per matching row.

The limits are real, though: every extra column makes each entry bigger, so fewer fit in a page and the index gets taller and consumes more cache. Covering a SELECT * is not a plan.

The redundancy that follows

If (a, b) exists, then (a) is redundant: anything the narrow index can serve, the wide one serves too, from the same leading column. Keeping both costs an extra write on every insert and every update to a, and buys nothing.

(b) is not redundant, because (a, b) cannot be entered on b.

So an index review is largely mechanical: list the indexes, and drop any whose column list is a leading prefix of another's. What is left is the set your workload actually needs — plus, usually, several that no query has used in years.

Interviewer's Next Move

The question after the question. This is what the round is actually testing.

1. "Does an index on (a, b) help WHERE b = ?" No. It is sorted by a first, so rows with a given b are scattered across every a block. The database can scan the whole index, but there is nothing to descend to — that is the leftmost prefix rule.

2. "Are (a, b) and (b, a) interchangeable?" No. They are different indexes with different capabilities. One serves WHERE a = ? and ORDER BY b within an a; the other serves neither.

3. "How do you choose the column order?" Equality columns first, then the one used for a range or ORDER BY. Only one range column can be used for seeking, and predicates after it filter row by row rather than narrowing the scan.

4. "Isn't the most selective column supposed to go first?" Only after usability. A very selective column that no query filters on is worthless in the leading position. Match the workload first; use selectivity to break ties.

5. "WHERE a = ? AND b > ? AND c = ? with an index on (a, b, c) — is c used?" Not for seeking. Once b is a range, entries are no longer grouped by c, so c is checked row by row. (a, c, b) uses all three.

6. "You have indexes on (customer_id) and (customer_id, status). Keep both?" No. The narrow one is a leading prefix of the wider one, so it is redundant — drop it and save a write per insert. An index on (status) alone would not be redundant.

7. "The query is indexed and still slow. What would you try before adding an index?" Widen an existing one to cover the selected columns. It removes a random read per matching row and costs the same one write per insert, because write cost is per index rather than per column.

8. "What is the cost of a covering index?" Bigger entries, so fewer per page, a taller index and more cache consumed. It is a good trade for a hot query with a short select list, and a bad one for SELECT *.

Code traps

Trap A — predict before you run:

CREATE INDEX idx_orders ON orders (status, customer_id);

SELECT * FROM orders WHERE customer_id = 42;

Trap B:

CREATE INDEX idx_events ON events (tenant_id, created_at, event_type);

SELECT * FROM events
WHERE tenant_id = 7 AND created_at > now() - interval '1 day' AND event_type = 'LOGIN';

Trap C:

CREATE INDEX idx_a ON payments (account_id);
CREATE INDEX idx_b ON payments (account_id, created_at);
CREATE INDEX idx_c ON payments (account_id, created_at, amount);

Common wrong answers

Check Yourself

Q1. Why can an index on (a, b) not serve WHERE b = ?

Q2. You filter on tenant_id and event_type with = and on created_at with a range. What column order?

Q3. When is a single-column index redundant, and when is it not?

Practice