Composite Indexes

A composite index (also called a multi-column or compound index) is a single index built across more than one column, rather than one column alone. It is useful whenever queries commonly filter or sort by the same combination of columns together, and it behaves differently from simply creating several separate single-column indexes.

Creating a composite index

A composite index on two columns

SQL

CREATE TABLE orders (
  order_id    INT PRIMARY KEY,
  customer_id INT,
  status      VARCHAR(20),
  order_date  DATE
);

CREATE INDEX idx_orders_customer_status ON orders (customer_id, status);

Column order matters: the leftmost prefix rule

A composite index is internally sorted first by its first column, then by its second column within each value of the first, and so on — much like a phone book sorted by last name, then first name within each last name. Because of that structure, the index can be used efficiently for queries that filter on a leading prefix of its columns, but not for queries that skip the first column and only filter on a later one.

What idx_orders_customer_status (customer_id, status) can and cannot help with

SQL

-- Can use the index efficiently: filters on the leftmost column
SELECT * FROM orders WHERE customer_id = 42;

-- Can use the index efficiently: filters on both columns, in order
SELECT * FROM orders WHERE customer_id = 42 AND status = 'shipped';

-- Cannot use this index efficiently: skips customer_id entirely
SELECT * FROM orders WHERE status = 'shipped';

That last query would need its own index on status alone (or one where status is the leftmost column) to benefit from an index at all. This is why column order in a composite index should be chosen based on real query patterns: put the column most commonly filtered alone, or filtered first, on the left.

Worked example: index order matching query order

Composite index designed around an actual query

SQL

-- Frequent query: recent orders for a given customer
SELECT * FROM orders
WHERE customer_id = 42
ORDER BY order_date DESC;

-- A composite index matching both the filter and the sort
CREATE INDEX idx_orders_customer_date ON orders (customer_id, order_date);

With this index, the database can jump straight to customer 42's rows, and because order_date is already sorted within that group, it can also satisfy the ORDER BY without a separate sorting step.

Composite index vs multiple single-column indexes

Situation	Better choice
Queries almost always filter on customer_id and status together	One composite index (customer_id, status)
Queries sometimes filter on customer_id alone, sometimes on status alone, rarely both	Two separate single-column indexes
Queries filter on customer_id and sort by order_date	One composite index (customer_id, order_date), covering both the filter and the sort

A composite index can often replace a redundant single-column index

If a composite index on (customer_id, status) already exists, a separate single-column index on customer_id alone is usually redundant, since the composite index can already serve any query that only filters on customer_id, thanks to the leftmost prefix rule. Keeping the redundant single-column index around just adds storage and write overhead without adding query capability.

A composite index spans multiple columns and is sorted by the first column, then the second within it, and so on.
The leftmost prefix rule means a composite index only helps queries that filter starting from its first column.
Order composite index columns to match real, common query filters and sorts — the most selective or most commonly filtered-alone column usually goes first.
A well-designed composite index can make a separate single-column index on its leading column unnecessary.