Composite Index 🔗 — Indexing Multiple Columns

A Composite Index is an index created using two or more columns. It is useful when queries frequently filter or sort using a combination of columns.

flowchart LR
    Q["Query"] --> I["Composite Index"]
    I --> C1["Column 1"]
    I --> C2["Column 2"]
    C1 --> T["Matching Rows"]
    C2 --> T

1. Simple Example

Suppose we frequently query orders using both CustomerId and OrderDate.

SELECT *
FROM Orders
WHERE CustomerId = 101
  AND OrderDate >= '2026-01-01';

We can consider a composite index:

CREATE INDEX IX_Orders_CustomerId_OrderDate
ON Orders(CustomerId, OrderDate);

2. Column Order Matters

The order of columns in a composite index is important.

(CustomerId, OrderDate)

This index is organized first by CustomerId and then by OrderDate within that key.

flowchart LR
    I["Composite Index"] --> C["CustomerId"]
    C --> D["OrderDate"]
    D --> R["Rows"]

3. Leftmost Prefix

For an index such as:

(CustomerId, OrderDate, Status)

Queries using the leading column or leading combination can potentially benefit from the index. Exact behavior depends on the database optimizer and query.

4. Composite Index vs Multiple Single-Column Indexes

These are not automatically equivalent.

flowchart LR
    T["Orders"]

    T --> I1["Index: CustomerId"]
    T --> I2["Index: OrderDate"]

    T --> I3["Composite Index: CustomerId + OrderDate"]

The right choice depends on the actual query patterns and the database optimizer. A composite index can be particularly useful when the same combination of columns is frequently used together.

5. Composite Index for Sorting

Composite indexes can also help queries involving filtering and ordering, depending on the index definition and query.

SELECT *
FROM Orders
WHERE CustomerId = 101
ORDER BY OrderDate DESC;

An index such as (CustomerId, OrderDate) may be useful for this access pattern.

6. Index Column Order — Interview Scenario

Suppose most queries are:

WHERE CustomerId = ?
  AND OrderDate BETWEEN ? AND ?

A reasonable starting point is:

(CustomerId, OrderDate)

But index design should ultimately be validated using the real workload and execution plans rather than choosing column order from a fixed rule alone.

7. Composite Index Has a Cost

Every index requires storage and must be maintained when indexed data changes.

flowchart LR
    W["INSERT / UPDATE / DELETE"] --> T["Table"]
    W --> I["Composite Index"]

    I --> C["Storage + Maintenance Cost"]

Therefore, create composite indexes for meaningful query patterns rather than indexing every possible column combination.

Important Interview Questions

• What is a composite index?
• Why do we use composite indexes?
• Why does column order matter?
• What is the leftmost-prefix concept?
• Composite index vs two single-column indexes?
• Can a composite index help with ORDER BY?
• What happens if you put the wrong column first?
• What are the costs of having many composite indexes?
• How would you choose the column order?

Key Takeaways

• Composite index = index on multiple columns.
• Column order matters.
• Leading columns are important for index usability.
• Composite indexes should match real query patterns.
• Indexes improve reads but add storage and write-maintenance cost.

Conclusion

For interviews, remember: Composite Index → Multiple Columns → Column Order Matters → Match Query Pattern.