Composite Indexes in Databases: How They Work and When to Use Them

If you've ever added an index on a single column and wondered why your multi-column WHERE clause is still slow, composite indexes are likely the answer.

What Is a Composite Index?

A composite index (also called a multi-column index) is an index on two or more columns of a table. Instead of indexing each column separately, the database builds a single B-tree structure that combines the values of all specified columns.

CREATE INDEX idx_orders_customer_date ON orders (customer_id, order_date);

This creates one index that covers both customer_id and order_date together.

How Column Order Matters

The order of columns in a composite index is critical. The index is sorted by the first column, then by the second column within each group of the first, and so on. Think of it like a phone book sorted by last name first, then first name.

Given the index (customer_id, order_date):

-- Uses the index (leftmost prefix match)
SELECT * FROM orders WHERE customer_id = 42;

-- Uses the index (both columns, left to right)
SELECT * FROM orders WHERE customer_id = 42 AND order_date > '2026-01-01';

-- Does NOT use the index efficiently
SELECT * FROM orders WHERE order_date > '2026-01-01';

The last query cannot use our composite index efficiently because order_date is the second column. Without filtering on customer_id first, the database would need to scan the entire index.

The Leftmost Prefix Rule

Most databases (PostgreSQL, MySQL, SQLite) follow the leftmost prefix rule: a composite index can be used for queries that filter on a left prefix of the indexed columns.

For an index on (a, b, c):

Composite Indexes and Sorting

A composite index can also speed up ORDER BY clauses that match the index order:

-- Uses the index for both filtering and sorting
SELECT * FROM orders
WHERE customer_id = 42
ORDER BY order_date;

-- Cannot use the index for sorting (wrong direction mismatch)
SELECT * FROM orders
WHERE customer_id = 42
ORDER BY order_date DESC, total_amount ASC;

If your query filters on the first column(s) and sorts by the next column in the index, the database avoids an expensive filesort operation.

When to Use Composite Indexes

Use them when:

• You frequently query with WHERE clauses that involve multiple columns together
• You have queries that filter on one column and sort by another
• You want to create a covering index that includes all columns a query needs, avoiding table lookups entirely

Avoid them when:

• The columns are rarely queried together
• The table is small enough that a full scan is negligible
• You already have too many indexes slowing down writes

Composite vs. Multiple Single-Column Indexes

A common misconception is that having separate indexes on customer_id and order_date is equivalent to a composite index on (customer_id, order_date). It is not.

With separate indexes, the database might use index merge (if supported), which scans both indexes independently and intersects the results. This is almost always slower than a single composite index that can navigate directly to the matching rows.

-- Two separate indexes: the database picks one OR attempts an index merge
CREATE INDEX idx_customer ON orders (customer_id);
CREATE INDEX idx_date ON orders (order_date);

-- One composite index: the database navigates directly
CREATE INDEX idx_customer_date ON orders (customer_id, order_date);

Choosing Column Order

Put the most selective (high-cardinality) column first when all columns use equality comparisons. If one column uses a range comparison (>, <, BETWEEN), place it last since the index cannot be used for columns after a range condition.

-- Good: equality first, range last
CREATE INDEX idx_example ON orders (status, customer_id, order_date);

-- Query that benefits from this order
SELECT * FROM orders
WHERE status = 'shipped'
  AND customer_id = 42
  AND order_date > '2026-01-01';

Verifying Index Usage

Always check your query plan to confirm the index is actually being used:

-- PostgreSQL
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42 AND order_date > '2026-01-01';

-- MySQL
EXPLAIN SELECT * FROM orders WHERE customer_id = 42 AND order_date > '2026-01-01';

Look for Index Scan or Index Only Scan (PostgreSQL) or ref/range in the type column (MySQL) to confirm the composite index is in play.

Key Takeaways

• A composite index is a single index on multiple columns, stored as one B-tree
• Column order matters: the leftmost prefix rule determines which queries can use the index
• Place equality columns before range columns
• A composite index is more efficient than multiple single-column indexes for multi-column queries
• Always verify with EXPLAIN that your index is being used as expected