You will require at least three indexes to support the above five queries. Those would be single field indexes on fields color , category and size respectively. These three indexes can support the five queries you had posted.
The second alternative is instead of the single field index on color, you can create a compound index on one (or both) of these - color+categoryandcolor+category+size.
But, there are couple of things to consider:
Indexes require disk space and when they are to used will be loaded in to the RAM memory - these are resources. More indexes and compound indexes can consume these resources and can affect the performance.
Another aspect is that the most important queries should be considered when creating your indexes - all queries may not need indexes.
Consider the indexing strategy of Selectivity before creating the compound indexes.
You will need some test data, create these indexes and try your queries. Then measure their usage and performance - for example, you can use explain to generate the query plans and study them.
I don’t think you can run explain on the countDocuments method, but countDocuments - mechanics says that underlying this method is the aggregation query:
Thanks for providing the detailed answer. I will continue to monitor the queries. Currently I feel a bit limited by the fact that I can not use .explain() in compas on aggregations. And when I use the explain() on aggregations in code, I need to read the raw json manually. But I hope this will change in the near future
A compound index on multiple fields can support all the queries that search a prefix subset of those fields, so the most efficient option to cover the three queries including color would be a single compound index on color+category+size.
If this compound index exists, you would want to drop any prefix indexes like color or color+category as they would add unnecessary overhead.
All five queries would optimally be supported by three indexes: