Composite Indexes

Composite indexes store multiple columns together in a defined order. Their usefulness depends on matching that order to common query filters, joins, and sorting needs.

A composite index, also called a multicolumn index, stores index entries using values from more than one column. For example, an index on `(customer_id, created_at)` can be useful for finding one customer's orders and reading them by date.

Column order matters. In many common B-tree implementations, an index on `(a, b)` is most useful when a query constrains `a`, optionally followed by `b`. It may also support a lookup on `a` alone, but generally cannot efficiently seek by `b` alone without a constraint on `a`.

Choose the order based on real query patterns, including filters, joins, and ordering. Equality constraints on leading columns are commonly followed by a range condition, such as `customer_id = ? AND created_at >= ?`. Exact behavior and additional optimizations depend on the database engine and index type.

Indexes consume storage and add work to inserts, updates, and deletes because the index entries must be maintained. A composite index should therefore solve a meaningful workload problem rather than merely include every column mentioned in a query.

Use query plans and representative data to verify that an index helps. An index definition alone does not guarantee the optimizer will choose it, or that the resulting query will be faster.

Key points

• A composite index stores multiple columns in a specific order.
• Queries that constrain leading columns are common candidates for using the index.
• Choose column order from actual filters, joins, and ordering requirements.
• Indexes can speed reads but add storage and write-maintenance costs.
• Verify index usefulness with query plans and workload measurements.

Interview questions

What is a composite index, and why does column order matter?

A composite index is one index whose key consists of multiple columns in a specified order. The order matters because common B-tree indexes are organized by the leading key first. For `(customer_id, created_at)`, the index can commonly support lookups by customer and then date, as well as lookups by customer alone. It generally is not a good direct access path for filtering only by `created_at`.

How would you choose the order for a composite index?

Start from the query workload rather than choosing columns by intuition alone. For equality filters on `customer_id` followed by a date range, `(customer_id, created_at)` is a reasonable candidate because the equality condition narrows the leading key before the range. If queries instead commonly search by date across all customers, a different index order may be more suitable. Validate candidates with representative data and the database's query plan.

How do you evaluate whether a composite index is worth keeping?

An index can reduce read work while increasing storage and write amplification, so evaluate its net value across the workload. I would identify expensive queries, inspect their plans and latency, then test candidate indexes under representative read and write load. I would also look for redundant or overlapping indexes, while accounting for differences in uniqueness, ordering, and included columns where the engine supports them. Keep an index only when its measured benefit justifies its maintenance cost.

Can an index on `(a, b)` help a query that filters only on `b`?

A composite B-tree index on `(a, b)` is usually ordered first by `a`, then by `b` within equal values of `a`. It can commonly support predicates on `a` and on both `a` and `b`, but not efficiently seek on `b` alone. Some engines can use skip-scan or other optimizations in particular circumstances, so the exact answer is engine-specific. Check the plan instead of treating the leftmost-prefix rule as an absolute law for every database.

Can a composite index help with an ORDER BY?

A query may use an index for filtering, ordering, or both, depending on the index ordering, predicates, and database engine. For `(customer_id, created_at)`, a query constrained to one customer may be able to return that customer's rows in date order without a separate sort. If it requests ordering across all customers by date alone, that index is generally not ordered appropriately. The query plan shows whether the index avoids a sort and what rows it scans.