What are the main principles used in constructing composite indexes in a database?

Composite indexes are created on multiple columns of a table and allow for faster queries that filter or sort data based on these columns.

Main principles of building composite indexes:

• The order of columns is important. The index is effective if queries use columns in the same order as they are listed in the index.
• Frequently used and selective columns are placed first. This increases search efficiency.
• Avoid including too many columns. This increases the size of the index and slows down insert/update operations.
• The index should match typical queries. Analyze which columns are most often used for filtering and sorting.

Example of creating a composite index in SQL:

CREATE INDEX idx_user_lastname_firstname ON users (lastname, firstname);

This index will be useful for queries where filtering or sorting is first done by lastname, then by firstname.