Composite indexes in SQL: why column order decides everything

You add an index on (status, created_at) and the query is still slow. You add another on (created_at, status) and suddenly it's fast. Same columns, very different results. Understanding why takes five minutes and saves hours of guessing.

A composite index is a sorted list

A B-tree index on (a, b, c) stores entries sorted by a, then by b within each a, then by c within each (a, b). Think of a phone book sorted by last name, then first name.

That ordering gives the leftmost-prefix rule: the index can be searched efficiently on a, on a and b, or on a, b and c. It can't be used efficiently to find rows by b alone, just as a phone book doesn't help you find everyone called "Priya" regardless of surname.

Equality first, then ranges, then sort

For a typical query:

SELECT id, total
FROM orders
WHERE customer_id = $1
  AND status = 'paid'
  AND created_at >= now() - interval '30 days'
ORDER BY created_at DESC
LIMIT 20;

A good index is:

CREATE INDEX orders_cust_status_created
  ON orders (customer_id, status, created_at DESC);

The rule of thumb:

1. Equality columns first (customer_id = ..., status = ...). They narrow the search to one contiguous slice of the index.
2. Then the range or sort column (created_at). Inside that slice, rows are already in date order, so the database can read the newest 20 and stop.
3. Columns after a range condition help much less. Once the index is scanning a range of created_at, later columns can't narrow the scan further.

Put created_at first and the database has to walk through every order in the last 30 days, for every customer, and filter. That's the slow version.

Check it with EXPLAIN

In PostgreSQL:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total FROM orders
WHERE customer_id = 42 AND status = 'paid'
ORDER BY created_at DESC LIMIT 20;

What to look for:

• Index Scan using orders_cust_status_created with no separate Sort node means the index provides the order directly.
• A Sort node above the scan means the index isn't matching your ORDER BY.
• A large difference between rows= estimates and actual rows suggests stale statistics; run ANALYZE orders.
• Rows Removed by Filter in the thousands means the index finds candidates but a condition is still applied row by row.

Covering indexes skip the table entirely

If the index contains every column the query needs, the database can answer from the index alone (an index-only scan in PostgreSQL). You can add non-key columns with INCLUDE:

CREATE INDEX orders_cust_status_created_cov
  ON orders (customer_id, status, created_at DESC)
  INCLUDE (total);

Use this for hot, read-heavy queries. Every extra column makes the index bigger and writes slower.

Low-cardinality columns: put them where they help

A common myth says "never index low-cardinality columns like status". On its own, an index on status is often useless. As the second column after a selective one, it is fine, because it splits each customer's orders into small groups. For a column where you only ever query one value, a partial index is even better:

CREATE INDEX orders_pending_created
  ON orders (created_at)
  WHERE status = 'pending';

It's small, and it matches the exact query your background job runs every minute.

Don't over-index

Each index slows down every INSERT, UPDATE and DELETE and uses memory. Before adding one:

• Check whether an existing index already covers the query through a leftmost prefix. An index on (customer_id, status, created_at) already serves WHERE customer_id = ?, so a separate (customer_id) index is redundant.
• Look at your real slow queries (pg_stat_statements in PostgreSQL, the slow query log in MySQL) rather than guessing.
• Drop indexes that are never used. PostgreSQL tracks this in pg_stat_user_indexes.idx_scan.

Summary

Order composite index columns as equality filters first, then the range or sort column, check the plan with EXPLAIN ANALYZE, and add INCLUDE or partial indexes only for queries that are hot enough to deserve them.