What are composite indexes used for?

Composite indexes accelerate queries that filter or sort by multiple columns simultaneously. They store pairs (or more) of values from different columns, allowing the DBMS to find the needed rows faster by scanning less data.

Examples of usage:

• Filtering by multiple criteria: WHERE column1 = value1 AND column2 = value2.
• Sorting by multiple columns: ORDER BY column1 ASC, column2 DESC.
• Some types of table joins (JOIN), where the ON condition includes multiple columns.

The order of columns in a composite index is important. Queries that use the first columns of the index will be more efficient.