The leftmost prefix rule
Run the four variants in order and watch the plan.
Both columns — SEARCH ... USING INDEX. The index leads with status, narrows to customer_id within it, and lands on the rows.
Leading column only — still SEARCH. All the paid rows are contiguous, so the index finds the block and reads it. A prefix of the index is itself a usable index.
Second column only — SCAN. This is the key one. The index exists, customer_id is in it, and the query can't use it for a seek, because rows for customer 42 are spread across each status value. There's no contiguous block to find.
Covering — USING COVERING INDEX, discussed below.
So an index on (a, b, c) can serve queries filtering on a, on a, b, or on a, b, c. It can't serve b, c, or b, c. Leading prefixes only.
The ordering rule that follows
Given that, the column order in a composite index is a design choice, not a detail. Two guidelines:
Equality before range. An index on (status, created_at) serves WHERE status = 'paid' AND created_at > ? well: the equality picks a block and the range narrows inside it. Reversed, the range comes first and all after it's scattered, so only the range column is really used.
Order for the queries you really run. If most queries filter by customer and only some also filter by status, lead with customer. Reading the workload is the only way to decide.
Covering indexes
The fourth variant selects only status and customer_id — both of which are already stored in the index. The plan says USING COVERING INDEX, and the table is never read at all.
This is worth understanding because of what an index most times costs. An plain index lookup happens in two stages: find the entries in the index, then fetch each matching row from the table. That second stage is random access, and on a large table it dominates. Returning a thousand rows can mean a thousand scattered reads.
A covering index drops the second stage fully. All the query needs is already in the sorted structure it just searched.
Some engines let you add columns purely for coverage without making them part of the sort key — PostgreSQL's INCLUDE, SQL Server's INCLUDE. That keeps the index narrow where it matters for searching while still avoiding the table.
Two indexes are not one index
A common mistake is to assume that indexes on (a) and (b) do the work of an index on (a, b).
They don't. Given both, a DB can search each apart and intersect the row sets, which some engines will do. But that means two searches and a merge, against one search on a composite index. It's materially worse, and for ORDER BY a, b the separate indexes provide no handy sorting at all.
The reverse substitution does work: (a, b) covers all (a) alone would have, so having both is often redundant.
The cost side
Indexes aren't free. each one must be updated on each insert, update and delete of an indexed column. So A table with eight indexes does roughly nine units of write work instead of one. They take space, they take memory in cache that data pages could have used, and an index no one queries costs all of that for nothing.
The hands-on loop is: find the slow query, read its plan, add the one index the plan asks for, read the plan again. Not: add indexes and hope.
Why two single indexes are not one composite
The claim that (a) and (b) can't replace (a, b) is worth making concrete, because it's where "but I indexed both columns" goes wrong. An index on (status, customer_id) is sorted by status first. So customer_id values for any one customer are scattered one per status — the phone-book-by-first-name problem. That decides which queries get a seek:
| query | (status, customer_id) |
|---|
WHERE status = ? AND customer_id = ? | SEARCH (both used) |
WHERE status = ? | SEARCH (leading prefix) |
WHERE customer_id = ? | SCAN (not a prefix) |
Given instead two separate indexes, (status) and (customer_id), the engine can search each and intersect the results. Two lookups and a merge, against one lookup on the composite — and neither gives the sorting for ORDER BY status, customer_id, so that still sorts. The composite is strictly more capable for queries that lead with status. This Is why column order is a design choice: it's chosen for the queries you really run, and it's the leading column that a second query can borrow, never the trailing one.
Questions people ask about Composite and Covering Indexes
What is the leftmost prefix rule? A composite index on (a, b, c) can serve queries filtering on a, on a and b, or on all three. It cannot efficiently serve a query filtering only on b.
Does column order in a composite index matter? Yes. Put equality filters first, then the range or sort column. The order decides which queries the index can help.
What is a covering index? An index that holds every column the query needs, so the database never reads the table rows. It is often shown as a covering index in the plan.
Are two single-column indexes the same as one composite index? No. The database can usually use only one of them well, while a composite index narrows on both columns together.
What is the cost of adding more indexes? Each index slows inserts, updates and deletes, and takes disk space. Add them for queries that run often.
Can a composite index help ORDER BY? Yes, if the index order matches the sort order after the equality columns, the database can read rows already sorted and skip the sort.
Where it goes wrong with Composite and Covering Indexes
Indexing each column apart. costly on write, and none of them serves a multi-column filter well.
Putting the range column first. All after it turns unusable.
Losing the sort. ORDER BY can use an index too, and a composite index that matches the sorting drops the sort step fully.
Leaving redundant indexes in place. If (a, b) exists, (a) is almost certainly dead weight. Drop it and reclaim the write cost.