What are the main principles of creating and using composite indexes for query optimization?

Composite indexes are created on multiple columns of a table and help optimize queries that filter or sort data based on these columns simultaneously.

Main principles:

• The order of columns in the index is important: the index is effective for queries that use the first columns of the index.
• An index assists with filtering, sorting, and joins on the specified columns.
• Do not create too many composite indexes — this increases the write load.

Example:

CREATE INDEX idx_user_lastname_firstname ON users (lastname, firstname);

This index speeds up queries like:

SELECT * FROM users WHERE lastname = 'Ivanov' AND firstname = 'Petr';

And also queries with a filter only on lastname. But queries only on firstname will not be accelerated by this index.