What is a composite index in a database?

A composite index (composite index) is an index created based on two or more columns of a table. It allows for efficient searching and sorting of data when query conditions use combinations of these columns.

Features:

• The order of columns in the index matters for query efficiency.
• The index can be used for queries that include a prefix of the columns in the index. For example, for an index on (A, B, C), a query with a condition on A or (A, B) can use this index. A query only on B or C is unlikely to use it efficiently.
• It reduces the number of scanned rows compared to using separate indexes on each column.

Example of creating a composite index in SQL:

CREATE INDEX idx_user_surname_name
ON users (surname, name);