Introduction

A composite index is an index built on more than one column, and it is most useful when your real queries filter, sort, or join on those columns together. The decision is not "more columns must be better"; it is "does this column order match the access pattern the database actually needs to optimize."

What a Composite Index Really Gives You

Suppose you have an orders table and queries like:

1SELECT order_id, total
2FROM orders
3WHERE customer_id = 42
4  AND order_date >= DATE '2025-01-01'
5ORDER BY order_date;

A composite index on (customer_id, order_date) can help because:

• the leading column narrows rows by customer
• the second column supports the date condition
• the same order can often help with sorting

Example index:

CREATE INDEX idx_orders_customer_date
ON orders (customer_id, order_date);

This is stronger than two separate single-column indexes when the database frequently needs both columns together in one plan.

The Leftmost-Prefix Rule

For common B-tree indexes, column order matters. An index on (customer_id, order_date) is naturally useful for:

• 'customer_id'
• 'customer_id plus order_date'

It is usually not as effective for queries filtering only on order_date, because the index is organized primarily by the first column.

That is the reason people say composite indexes follow a leftmost-prefix rule. You do not just choose the columns; you choose their order.

Consider these two indexes:

CREATE INDEX idx_a ON orders (customer_id, order_date);
CREATE INDEX idx_b ON orders (order_date, customer_id);

They are not interchangeable. The right one depends on which predicates are most selective and which query shapes occur most often.

Good Cases for a Composite Index

Use a composite index when one or more of these are true:

• queries regularly filter on the same column combination
• a frequent sort order follows the same columns
• joins and filters repeatedly pair the same keys
• the index can cover the query well enough to reduce table lookups

For example, an activity log table often benefits from (user_id, created_at) because applications routinely ask for one user's events ordered by time.

1SELECT created_at, action
2FROM activity_log
3WHERE user_id = 7
4ORDER BY created_at DESC
5LIMIT 50;

That query shape is an excellent candidate for a composite index with the same logical ordering.

When Separate Indexes Are Better

Do not create a composite index just because two columns exist in the same table. If your workload contains unrelated queries such as:

• one query filters only by email
• another filters only by status

then separate indexes may be more appropriate than a combined (email, status) index.

Composite indexes are specialized tools. If the columns are not used together consistently, the extra write overhead may buy very little.

Composite Indexes and Sorting

A composite index can remove an expensive sort if the WHERE and ORDER BY clauses align with the index order.

1SELECT customer_id, order_date, total
2FROM orders
3WHERE customer_id = 42
4ORDER BY order_date DESC;

An index beginning with customer_id and then order_date can help the database locate matching rows in the needed order. That is often faster than filtering rows first and sorting later.

Be careful, though: once query shapes diverge, a single index cannot optimize everything. Designing one "universal" composite index usually produces mediocre results instead of one excellent result.

Cost of Over-Indexing

Every index makes writes more expensive because inserts, updates, and deletes must maintain the index structure. Wider composite indexes also consume more storage and memory.

That tradeoff matters on high-write tables. If a table receives heavy insert traffic, adding several multi-column indexes can degrade throughput noticeably. Indexing must be justified by query patterns, not by habit.

A Practical Design Process

Start from slow queries, not from schema aesthetics:

1. inspect the real WHERE, JOIN, and ORDER BY clauses
2. identify repeated column combinations
3. choose an index order that matches those combinations
4. verify with EXPLAIN

Example:

1EXPLAIN
2SELECT order_id, total
3FROM orders
4WHERE customer_id = 42
5  AND status = 'PAID'
6ORDER BY order_date DESC;

If this pattern is frequent, (customer_id, status, order_date) may be reasonable. If status has very low selectivity and customer_id does most of the filtering, the order should reflect that reality.

Common Pitfalls

• Creating a composite index with the right columns in the wrong order. Column order is often the deciding factor in whether the index helps.
• Assuming an index on (a, b) is equally good for queries on only b. For B-tree indexes, the leading column matters.
• Replacing all single-column indexes with one wide composite index. Different query shapes may still need different support.
• Ignoring write overhead on insert-heavy or update-heavy tables. Every additional index adds maintenance cost.
• Adding the index without checking the execution plan. Use EXPLAIN to confirm the optimizer actually benefits from it.

Summary

• Use a composite index when queries consistently filter, sort, or join on the same column combination.
• The leftmost column order matters as much as the column list itself.
• Composite indexes are especially valuable when they help both filtering and ordering.
• They are not automatically better than separate indexes for unrelated query patterns.
• Design them from observed workload and verify with execution plans, not guesswork.