Published on

What is a Composite Index? Optimizing Multi-Column Queries in Databases

What is a Composite Index?

A Composite Index (also called a multi-column or concatenated index) is an index built on two or more columns combined as a single search key.

For example, an index on (LastName, FirstName) treats the pair as one key, effectively sorting and searching by LastName first, then FirstName.

This allows queries filtering on multiple columns together to use a single index lookup rather than combining separate single-column indexes.

How Does a Composite Index Improve Performance?

  • Speeds up multi-column queries: If a query filters on columns A and B, a composite index on (A, B) can directly locate matching pairs efficiently.
  • Reduces index intersection: Using separate single-column indexes might require intersecting results or extra filtering.
  • Supports sorting and grouping: Queries with ORDER BY or GROUP BY on multiple columns benefit from composite indexes.
  • Can act as a covering index: If it includes all needed columns, queries can be answered from the index without accessing the table.
  • Saves maintenance and storage: One composite index can replace multiple single-column indexes when queries commonly use those columns together.

Important: Column Order Matters!

Databases typically use the leftmost prefix rule, meaning:

  • The index can be used for queries filtering on the first column, or the first and second, and so on.
  • The index cannot efficiently support queries filtering on a later column alone without the earlier columns.

Example: An index on (A, B) can be used for:

  • Queries on A alone,
  • Queries on A and B together,

but not for queries on B alone.

Because of this, choose the column order based on your most common query patterns.

When Should You Use Composite Indexes?

Use composite indexes when your queries frequently involve filtering or sorting on multiple columns together, such as:

  • Addresses: Index on (State, City) if queries filter by both.
  • Orders: Index on (CustomerID, OrderDate) to support queries by customer and date ranges.
  • Uniqueness: Enforce unique constraints on multiple columns by creating a unique composite index (e.g., (FirstName, LastName)).

If you often query the second column independently, you might still need a separate single-column index on it.

Example

Suppose you have an Orders table and frequently run queries filtering by both CustomerID and OrderDate:

-- Create composite index on CustomerID and OrderDate
CREATE INDEX idx_orders_cust_date ON Orders(CustomerID, OrderDate);

This index speeds up queries like:

SELECT * FROM Orders
WHERE CustomerID = 42
  AND OrderDate >= '2024-01-01'
  AND OrderDate < '2024-02-01';

It will also optimize queries filtering by CustomerID alone.

However, queries filtering only by OrderDate cannot efficiently use this index because OrderDate is the second column in the composite key.

In summary: Composite indexes are powerful tools for accelerating queries involving multiple columns, but their effectiveness depends on the column order and how your queries filter the data. Choosing the right columns and order for the composite index can yield major performance benefits.