Suppose I have the following schema:-
collection name- Test
{
"uid": "5e0e6a804888946fa61a1976",
"name": "abc",
"bookName":"xyz",
"rating": 5,
"category":"aa",
"isbn":"45656"
}
my main query is to find the test collection by "uid".
The use case is that, a user can filter based on no. of fields and any combination.
for eg 1:. he selects name :'abc', and selects 'rating' as '5',
next eg 2:; he can select cat: "some cat", and name :"some name".
What is the efficient way to have indexes/compound indexes on the patterns? which will work on any combination and any no. of selected fields by user. How can i make it to cover the query using indexes?
currently for eg1: i can create index- {name:1, rating:1}
for eg2: i can create index- {cat:1, name:1}
But in a case where no order is defined on how a user can select the filters? How can I solve it? Do I need to create all of the indexes with each combination possible?
Please help, and suggest any ideas.
My query is dynamic on find based on users selection on filters but uid is constant.
CodePudding user response：
There is no silver bullet for dealing with 'any number of fields in any combination' kind of scenario. You'll have to decide what queries are the most critical for your application, and create indexes that support them. Less frequent queries may be better off only partially supported.
The best overall strategy for designing indexes is to profile a variety of index configurations with data sets similar to the ones you'll be running in production to see which configurations perform best. Inspect the current indexes created for your collections to ensure they are supporting your current and planned queries. If an index is no longer used, drop the index.[1]
Why not just create all of the indexes for each possible fields combination? Because
Indexes come with a performance cost, but are more than worth the cost for frequent queries on large data sets. Consider the relative frequency of each query in the application and whether the query justifies an index.[1] (emphasis mine).
So, you'll need to either carefully plan your queries ahead, or set up a staging environment and observe the query patterns as they emerge, and trim the indexes which don't justify their existence.
Finally, my personal advice is to make sure that you understand the ESR rule[2], and create several compound indexes keeping this rule in mind, then keep an eye on how your MongoDB deployment performs. Data access patterns may end up being not as scarily arbitrary as they originally seemed.
And by no means I'm telling you that ESR rule is the only thing you need to know in order to come up with an efficient indexing strategy. There is much more to it. [3]
[1] https://www.mongodb.com/docs/manual/applications/indexes
[2] https://www.mongodb.com/docs/manual/tutorial/equality-sort-range-rule
[3] https://www.mongodb.com/blog/post/performance-best-practices-indexing