MongoDB indexes on multiple fields

Question

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?

Sammaye · Accepted Answer · 2013-03-02 18:31:48Z

6

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.

answered Mar 2, 2013 at 18:31

Sammaye

44k7 gold badges110 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Thomas Mitchell Over a year ago

Ah, I forgot about explain. I'll do some testing after adding the a,b,c,d,e index and see how it effects things.

Collectives™ on Stack Overflow

MongoDB indexes on multiple fields

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related