Composite Indexes

Composite indexes index multiple columns together and can speed up queries that filter, sort, or join using those columns. Their usefulness depends heavily on column order and how the database executes the query.

A composite index contains two or more columns in a defined order, such as `INDEX (last_name, first_name)`. The database can use that ordered structure to locate matching rows more efficiently than scanning the entire table.

Column order matters because the index is organized starting with its first column. An index on `(last_name, first_name)` is generally useful for queries filtering by `last_name`, or by both `last_name` and `first_name`. It is usually less useful for a query filtering only by `first_name` because the leading `last_name` portion is not constrained.

Composite indexes can also support ordering and range conditions when the query matches the index's ordering rules. For example, an index on `(customer_id, created_at)` can help retrieve a customer's records ordered by creation time, depending on the database and query plan.

Indexes have costs as well as benefits. They consume storage and add work to `INSERT`, `UPDATE`, and `DELETE` operations because the index must be maintained. A good index should therefore reflect common, selective query patterns rather than simply indexing every column combination.

Key points

• A composite index contains multiple columns in a specific order.
• The leftmost or leading columns are especially important for index usability.
• Column order should reflect common query predicates and access patterns.
• Composite indexes can help filtering, ordering, and some range queries.
• Indexes improve reads but add storage and write-maintenance costs.

Interview questions

What is a composite index?

A composite index is an index built from two or more columns. The columns have a specific order inside the index. It is useful when queries commonly use those columns together for filtering, sorting, or related access patterns. The order matters because the database can efficiently use the leading portion of the index.

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

A composite index is ordered first by its first column, then by the next column within matching values of the first. Therefore, an index on `(customer_id, created_at)` naturally supports lookups beginning with `customer_id`. A query that only constrains `created_at` generally cannot use that index as effectively because it does not constrain the leading column.

How would you choose the columns for a composite index?

Start with the application's important queries rather than choosing columns arbitrarily. Consider which columns are commonly used in equality predicates, range predicates, joins, and ordering, then choose an order that matches those access patterns. Selectivity is also relevant, but it should be considered together with the actual workload and query plans. I would validate the design with representative data and an execution-plan tool.

Can a composite index be used when a query filters only on the second column?

Usually, an index such as `(a, b)` is most useful when the query constrains `a` or both `a` and `b`. A query that only constrains `b` cannot normally perform the same efficient ordered lookup because the index is primarily organized by `a`. Some database engines can use additional techniques in particular circumstances, so the execution plan should be checked rather than relying on a blanket assumption.