What Is a Composite Index in a Database?

A composite index (also called a multi-column index) is a single database index built across two or more columns rather than just one, storing entries sorted by the first column, then by the second within ties on the first, and so on. It’s a natural extension of a single-column index, but its behavior — and the mistakes people make with it — hinges almost entirely on the order the columns are declared in.

Why one index across multiple columns

Most B-tree indexes, which are the default index type in nearly every relational database, are built for exactly this: a sorted structure that can be searched quickly by leading columns. A composite index on (customer_id, created_at) stores rows sorted first by customer_id, and within each customer_id, sorted by created_at. That makes it extremely efficient for a query like “all orders for this customer, most recent first” — the database can jump straight to the right customer_id range and the rows are already sorted by date within it.

You could achieve something similar with two separate single-column indexes, one on customer_id and one on created_at, and let the query planner combine them. But combining two separate indexes at query time is generally slower than reading one index that was built to answer exactly this compound question, especially as the table grows.

Column order determines what the index can do

This is the single most important thing to understand about composite indexes: an index on (a, b) can efficiently serve queries that filter on a alone, or on a and b together, but it generally cannot efficiently serve a query that filters on b alone.

That’s because the index is sorted by a first. Within the sorted structure, values of b are only sorted locally within each group of matching a values — across the whole index, b values are effectively scattered. Searching for a specific b value without also constraining a means the database can’t use the index’s sort order to narrow the search; it would have to scan the whole thing.

This is sometimes called the “leftmost prefix rule”: a composite index on (a, b, c) can serve lookups on a, on (a, b), or on (a, b, c), but not on b alone, c alone, or (b, c) without a. Getting this order wrong is the most common reason a composite index exists in a schema but never actually gets used by the query planner.

A concrete example

Consider a table of orders with an index on (customer_id, status):

CREATE INDEX idx_orders_customer_status
  ON orders (customer_id, status);

This index efficiently serves:

SELECT * FROM orders WHERE customer_id = 42;
SELECT * FROM orders WHERE customer_id = 42 AND status = 'shipped';

But it does little to help this query, because status alone isn’t a leftmost prefix of the index:

SELECT * FROM orders WHERE status = 'shipped';

If both query shapes are common in your application, you likely need two separate indexes — one starting with customer_id, another starting with status — rather than trying to make one composite index serve both.

Composite indexes vs multiple single-column indexes

It’s tempting to assume that creating a single-column index on every filterable column is “safe” and lets the query planner figure out the best combination at query time. Most databases can combine multiple single-column indexes for a query — often through a bitmap-style intersection, conceptually related to how a bitmap index works — but this is generally slower than one well-ordered composite index built for the actual query pattern, because it means reading and merging results from multiple separate structures instead of one sequential scan through pre-sorted data.

The practical guidance: build composite indexes around your application’s actual, known query patterns — the WHERE clauses and ORDER BY clauses you run frequently — rather than one index per column and hoping the planner sorts it out. Look at real query patterns, including how you join tables and what you filter on most, before deciding column order.

Covering indexes: the natural next step

A composite index can go further than just speeding up the search — if it includes every column a query actually selects, the database never needs to touch the underlying table row at all, satisfying the entire query from the index itself. This is called a covering index, and it’s one of the biggest wins available from composite indexing, since it eliminates an entire round of lookups. See what a covering index is for how this works in more depth and when it’s worth the extra storage overhead.

The costs: write overhead and storage

Every index — composite or single-column — has to be updated on every insert, update, and delete that touches its columns, and a composite index spanning more columns means more data to maintain per write. On tables with heavy write traffic, indiscriminately adding wide composite indexes can measurably slow down writes even as it speeds up specific reads. This tradeoff is one reason index design isn’t a one-time decision — as query patterns shift, especially on large, actively partitioned or sharded tables, existing indexes are worth periodically revisiting rather than only ever adding new ones.

The takeaway

A composite index stores rows sorted by multiple columns in a fixed order, and that order determines exactly which queries it can accelerate — only queries filtering on a leftmost prefix of the indexed columns benefit. Build composite indexes around your application’s real query shapes rather than indexing every column individually, consider extending them into covering indexes when a query’s selected columns are known and stable, and weigh the write overhead against the read speedup on tables with heavy write traffic.