What index should be added for a search query for free books? Why is each field included in the composite index? How will the index perform with 2 million rows?

To optimize a search query for available books, it is best to create a composite index that includes the fields used for filtering and sorting. For example, if you have a table books with fields status (available or occupied), category, and title, and the query looks like this:

SELECT * FROM books WHERE status = 'free' ORDER BY category, title;

then the index could be created as follows:

CREATE INDEX idx_books_status_category_title ON books (status, category, title);

The purpose of each field:

• status — first in the index, as it is used for filtering (WHERE), to quickly select only free books.
• category — second, to speed up sorting and grouping by category.
• title — third, for sorting within each category.

With 2 million rows, such an index will allow quick filtering of the necessary records without scanning the entire table, and then efficiently sort the results. The index reduces the number of read operations and speeds up query execution, especially if the number of free books is significantly less than the total.