5

I've read the chapter on indexes in the MongoDB in action book and was wondering if anyone can expand upon what it talks about regarding indexes.

If I have an index that covers a,b,c,d,e and I query on a,b,c the index is used. What happens if I query on a,c,e? Is the index just used for the query on a or does it get used when querying on the other fields?

In this case does it make more sense to also have the index on a,c,e. I ask because I have a front end piece that links to these fields where users can create a free form query (a,b,c,f could be one). Do I need an index for all possible options that could come through?

Zaid Masud
  • 13,225
  • 9
  • 67
  • 88
Thomas Mitchell
  • 1,071
  • 2
  • 16
  • 29

1 Answers1

5

Compound indexes in MongoDB work via a prefix method.

So for an index of a,b,c,d,e a and a,b would be considered prefixes ( http://docs.mongodb.org/manual/core/indexes/#id5 ), as such querying on both a,b,c and a,c,e should yield an index usage; as to whether a,c,e uses the index performantly is another question entirely.

I ask because I have a front end piece that links to these fields where users can create a free form query (a,b,c,f could be one). Do I need an index for all possible options that could come through?

Hmm if a is always first field defined then probably not (an explain would of course be better here on your dataset), otherwise you may require some combinations, yes.

Sammaye
  • 43,242
  • 7
  • 104
  • 146