What is a composite index?

A composite index, also known as a concatenated index, is a type of index in a database that includes more than one column from a single table. It is designed to improve the performance of queries that frequently filter or sort data based on multiple columns simultaneously.

What is a Composite Index?

A composite index is an index created on two or more columns of a table. Unlike a single-column index, which provides a fast lookup path for a single field, a composite index allows the database system to efficiently search, sort, and retrieve data across a combination of fields. The order in which columns are specified when creating the index is crucial, as it dictates the storage order and influences its effectiveness for various query patterns.

When a query involves conditions on the leading columns (or a prefix of the columns) in a composite index, the database can use this index to quickly locate the relevant rows, often avoiding full table scans. For instance, an index on (columnA, columnB, columnC) can be used for queries filtering on (columnA), (columnA, columnB), or (columnA, columnB, columnC).

The primary benefit of a composite index is to optimize queries that have WHERE clauses involving multiple columns, or queries that need to sort results by multiple columns. It can also be used to enforce uniqueness across a combination of columns, ensuring that no two rows have the same values for the indexed fields.

When to Use a Composite Index

• Frequent Multi-Column Filtering: If your queries often filter data using WHERE column1 = 'value1' AND column2 = 'value2', a composite index on (column1, column2) can be highly beneficial.
• Sorting and Grouping: For queries that ORDER BY or GROUP BY multiple columns, a composite index matching these columns can speed up the operation by providing pre-sorted data.
• Covering Indexes: A composite index can act as a covering index if it includes all the columns requested in the SELECT clause (and WHERE clause). In such cases, the database can retrieve all necessary data directly from the index without accessing the actual table rows, significantly improving performance.
• Unique Constraints: To enforce uniqueness across a combination of columns (e.g., ensuring a user can only have one review per product), a unique composite index is used.

Example

Consider an orders table where you frequently search for orders by customer_id and order_date.

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    total_amount DECIMAL(10, 2),
    status VARCHAR(50)
);

CREATE INDEX idx_customer_order_date ON orders (customer_id, order_date);

This idx_customer_order_date composite index will accelerate queries like SELECT * FROM orders WHERE customer_id = 123 AND order_date > '2023-01-01'; or SELECT * FROM orders WHERE customer_id = 123 ORDER BY order_date DESC;. It would also be useful for SELECT * FROM orders WHERE customer_id = 123; because customer_id is the leading column.

Important Considerations

• Column Order: The order of columns in a composite index is critical. The most selective columns (those that narrow down results the most) or columns most frequently used in WHERE clauses should generally come first. An index on (A, B) is not the same as (B, A). An index on (A, B) can be used for queries on A alone or A and B, but not B alone.
• Index Overhead: Like all indexes, composite indexes consume disk space and add overhead to data modification operations (INSERT, UPDATE, DELETE) because the index structure must also be updated. Create them judiciously.
• Query Patterns: Analyze your common query patterns to determine which columns are most frequently used together in WHERE, ORDER BY, or GROUP BY clauses before creating composite indexes.