SQL Composite Index

A composite index is a database index built from multiple columns, used to speed up queries that filter, sort, or join using those columns. The order of columns in the index matters because many database engines can efficiently use the leading columns first.

A composite index contains two or more columns in a defined order, such as `CREATE INDEX idx_users_name_email ON users(last_name, first_name)`. The database maintains index entries according to that column sequence rather than treating the columns as unrelated indexes.

Column order is important because of the leftmost-prefix principle commonly used by B-tree indexes. An index on `(last_name, first_name)` can generally help with conditions involving `last_name`, or both `last_name` and `first_name`, but it is usually not as useful for a query filtering only on `first_name`.

Composite indexes can also support `ORDER BY` operations when the requested ordering matches the index's structure and the query's other conditions allow the optimizer to use it. They can reduce the number of rows the database must inspect, but an index is not automatically beneficial for every query.

When designing one, consider the queries the application actually runs. A Java backend might frequently execute `WHERE customer_id = ? AND status = ?`, making an index such as `(customer_id, status)` potentially useful. Avoid creating many overlapping indexes because indexes consume storage and add work to inserts, updates, and deletes.

Key points

• A composite index contains multiple columns in a specific order.
• Column order affects which query predicates can efficiently use the index.
• An index on `(A, B)` generally supports searches on `A` and `(A, B)` better than searches on only `B`.
• Composite indexes can help filtering and sometimes sorting, depending on the query and database optimizer.
• Indexes improve reads at the cost of storage and additional write overhead.

Interview questions

What is a composite index?

A composite index is an index containing multiple columns. For example, `(customer_id, status)` can support queries that filter by both columns. The order of those columns is significant because the index is organized according to that sequence. It can improve lookup performance while adding storage and write-maintenance costs.

Why does the order of columns matter in a composite index?

A B-tree-style index is organized using the first indexed column and then subsequent columns within that ordering. Therefore, an index on `(last_name, first_name)` can usually be used efficiently when `last_name` is part of the search condition. A query filtering only on `first_name` generally cannot use that index as directly as an index beginning with `first_name`. The best order depends on the application's query patterns.

Is a composite index always better than two single-column indexes?

No. The appropriate choice depends on the queries and database optimizer. A composite index can efficiently represent a common multi-column access pattern, while separate indexes can be more flexible for queries that independently search each column. Index design should be based on actual workload and query plans rather than assuming one structure is universally better.

How can a composite index affect INSERT and UPDATE performance?

When indexed columns change or new rows are inserted, the database must maintain the relevant index structures. That creates additional work compared with having no such index. The impact depends on the database engine, workload, and number and size of indexes. Consequently, indexes should be chosen to provide meaningful read benefits rather than added indiscriminately.