CODEWITHSUNDEEP
mongodb
YMC
YMC

Reputation: 5462

Need to build a proper composed index for MongoDb

I have a huge collection of millions of records, there will be 4 fields used to fetch data, lets say f1, f2, f3 for filtering and f4 for data sorting. There will be 3 types of queries: 

1) filter by f1 AND f2, and sort by f4
2) filter by f1 AND f2 AND f3, and sort by f4
3) filter by f1 AND f2 AND few additional fields, and sort by f4

So sorting is always by f4. First 2 types of queries will be mostly used. Additional fields in the case 3 might be any field in the collection (search functionality), so I'm not going to use them for indexing.

My suggestion is that the only composed key (f1, f2, f3, f4) might be the best solution, but as I have almost no experience in mongoDb I decided to ask you guys.

Upvotes: 1

Views: 119

Answers (1)

slee
slee

Reputation: 534

Start with {f1:1,f2:1,f4:1} and {f1:1,f2:1,f3:1,f4:1} for the first two queries. You can always use the .find(...).sort({f4:1}).explain(), to evaluate the appropriateness of the chosen index for the given query. Moreover, you might find that some of your queries on f1 AND f2 AND few additional fields query might benefit from additional indexes like {f1:1,f2:1,addField1:1,f4:1}.

Also, pay attention to whether you use f4:1 or f4:-1, depending on whether you always sort by f4 in ascending or descending order.

Upvotes: 2

Related Questions