If there is a query that filters on field a and b and then order on c, do I need to build separate indexes for a, b, and c, or should I actually build a compound index of (a, b, c)? And also, does the should the sequence in the query match the sequence in the index? that is if the filter sequence in the query is filter b, filter c, and then order on a, then should it better to have compound index of (b, c, a)?
With MongoDB, what are the fields that need to be picked for building indexes on?
204 Views Asked by tom At
2
There are 2 best solutions below
3
On
All of this and more is answered in the docs. See the sections on compound indexes.
The order of the query params doesn't matter.
Since MongoDB currently uses only one index per query, you will need a compound index.
The order of the index params does matter, although not necessarily in the way you mention in the question.
Since filtering happens first, if the index was (c,b,a), it wouldn't be very useful for filtering, especially if there are a lot of items in the collection. The fields used for sorting should be specified last in the index.
So the index should either be (a,b,c) or (b,a,c). Which one of those it should be depends on selectivity -- in other words, which field will eliminate items that don't match faster?
If there are 10,000 likely values for b, and only two likely values for a, then the index should be (b,a,c). Conversely, if there are many more possible values for a, then it should probably be (a,b,c). If the two fields are roughly the same in their ability to eliminate documents from the query, then it won't matter that much.