Ask HN: Optimizing database table for user-made queries?

Let's say you have have a large-ish database table of product orders, and you'd like to allow users to query it with an API, using several different filter and sort options, and of course supporting pagination.

What would be the best strategy to optimize such query? Creating an index for every possible combination of options doesn't seem practical. How do large companies handle queries like [0], with 20 different filters and 10 different sort options?

In our particular case, we use a MySQL db in AWS RDS.

Tangentially, can you recommend any good books/resources with real-world usage patterns, and examples of the entire process of analyzing and solving database-related problems in growing web applications? Ideally written from the problem perspective, rather than just a reference of available db functionalities.

Thanks!

[0] https://shopify.dev/api/admin-graphql/2022-01/objects/Product#query-products