Can you describe a situation where using a composite index in a database would be particularly beneficial?

A composite index is especially useful when queries frequently filter or sort by multiple columns simultaneously. For example, if you have an orders table with columns customer_id and order_date, and you often run queries like:

SELECT * FROM orders WHERE customer_id = ? AND order_date > ? ORDER BY order_date DESC;

creating a composite index on (customer_id, order_date) can significantly speed up such queries, as the database can efficiently use the index to search across both columns at once. This is better than creating separate indexes on each column, as a composite index considers the order of columns and optimizes such combined queries.