Composite Index

A composite index stores multiple columns in a defined order to support queries that use those columns. Its column order matters because the database can efficiently use the index according to its leading columns.

A composite index contains two or more columns, such as `CREATE INDEX idx_user_status_created ON users(status, created_at)`. The database keeps index entries ordered by the first column, then by the second within matching first-column values.

The order determines which queries can benefit efficiently. An index on `(status, created_at)` can generally help with conditions on `status`, or on both `status` and `created_at`, but it is not equivalent to an index on `(created_at, status)` for a query that filters only by `created_at`.

Composite indexes are especially useful when a query repeatedly filters, sorts, or joins using the same group of columns. A well-chosen index can reduce the number of rows the database must examine, but indexes also consume storage and add work when rows are inserted, updated, or deleted.

For a Java backend engineer, the important practical skill is matching index definitions to real SQL query patterns. Use `EXPLAIN` or the database's execution-plan tools to verify whether the optimizer actually chooses the index rather than assuming that an index will always improve performance.

Key points

• Column order is significant in a composite index.
• The leftmost or leading column can often be used independently.
• Composite indexes can support filtering and, depending on the query and database, ordering.
• Choose indexes from actual query patterns rather than simply indexing every filtered column.
• Indexes improve reads at the cost of storage and write overhead.

Interview questions

What is a composite index?

A composite index is an index built from multiple columns. The columns have a specific order, and that order affects which queries can use the index efficiently. For example, `(status, created_at)` can support queries that constrain `status` and then work with `created_at`. It can reduce the amount of data the database needs to inspect.

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

The index is organized according to the first column and then subsequent columns within that ordering. Therefore, `(status, created_at)` is naturally useful when a query narrows by `status` before using `created_at`. Reversing the columns creates a different index structure. The best order depends on the query workload and database behavior.

Given an index on `(status, created_at)`, which queries can it help?

A query filtering on `status` can generally use the leading portion of the index. A query filtering on both `status` and `created_at` can generally make more complete use of the index. A query that only filters on `created_at` cannot generally use this index as effectively because `created_at` is not the leading column. The exact plan should be confirmed with the database's execution-plan tool.

When would you choose a composite index instead of two single-column indexes?

I would choose a composite index when the application's important queries repeatedly use the columns together and the index ordering matches those query patterns. A composite index can provide an ordered structure that separate indexes do not replicate. I would also consider whether the index can support filtering and sorting together. Finally, I would validate the choice using execution plans and representative workload data.

What are the trade-offs of adding a composite index?

The main benefit is faster access for queries that match the index's structure. The costs include additional disk or memory consumption and maintenance work when indexed rows are inserted, deleted, or updated. A poorly chosen index can provide little benefit while still imposing those costs. I would therefore base index design on measured query behavior, workload characteristics, and execution plans rather than indexing every possible predicate.