Optimizing database table for user-made queries?

Question

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

prirun · Accepted Answer

Database software often will create automatic indexes based on the queries it gets. Even SQLite has this:
https://www.sqlite.org/optoverview.html#autoindex
If your db is read-mostly, manually adding a bunch of indexes based on a statistical analysis of actual queries might be useful. If you have a lot of writes it becomes a tricky balancing act because every index has the potential to slow down writes while speeding up reads.

franzwong · Answer

I think they use both no-sql and relational database.