1

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.

YMC
  • 4,925
  • 7
  • 53
  • 83
  • Are you asking for a compound index? If so, does this help: http://stackoverflow.com/questions/9901787/how-to-structure-a-compound-index-in-mongodb – themanatuf Sep 24 '12 at 19:46
  • It is similar question, but the difference is I have no range queries, only like `{ f1:2, f2:3 }` or like `f1:{in:[2,3,4]}`, so my question is mostly about necessity of creating index for every case described above, or one complex index (f1, f2, f3, f4) would cover all the cases. According to @slee answwer, I have to create separate index for every case – YMC Sep 24 '12 at 20:51
  • Ok, it turned out $in is a range query, that's what I've found: http://www.deanlee.cn/programming/mongodb-optimize-index-avoid-scanandorder/ – YMC Sep 24 '12 at 23:41

1 Answers1

2

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.

slee
  • 524
  • 2
  • 6