What is a composite index?

A composite index (or compound index) is a database index built from the values of two or more columns of a table.

Main characteristics and advantages:

• Query coverage: It can speed up queries that filter, sort, or group data based on a combination of included columns.
• Column order: The order of columns in the index definition matters. Queries that use only the first column or the first few columns in their original order can efficiently utilize the index (leftmost prefix rule).
• Reducing the number of indexes: It allows replacing multiple single-column indexes with one composite index.

Example of creating a composite index in MySQL:

CREATE INDEX idx_lastname_firstname
ON employees (last_name, first_name);

In this example, the index idx_lastname_firstname is created based on the columns last_name and first_name. It will be efficiently used for queries like:

SELECT * FROM employees WHERE last_name = 'Smith' AND first_name = 'John';
SELECT * FROM employees WHERE last_name = 'Smith';

But it may be less effective for queries that only use first_name.

Proper design of composite indexes is critical for database performance.