What is a composite index (index on multiple columns)?

A composite index is a database index that is created on multiple columns of a table simultaneously. Such an index helps to speed up queries that filter or sort data based on a combination of these columns.

For example, if you have a table with fields first_name and last_name, a composite index on (last_name, first_name) will help quickly find records where both fields are considered.

It is important to remember that the order of columns in a composite index matters: an index on (last_name, first_name) is effectively used for queries filtering by last_name or both fields, but not for queries filtering only by first_name.