compound Index or single index in mongodb

Question

I got a query like this that gets called 90% of the times:

db.xyz.find({ "ws.wz.eId" : 665 , "ws.ce1.id" : 665)

and another one like this that gets called 10% of the times:

db.xyz.find({ "ws.wz.eId" : 111 , "ws.ce2.id" : 111)

You can see that the id for the two collections in both queries are the same. Now I'm wondering if I should just create a single index just for "ws.wz.eId" or if I should create two compound indexes: one for {"ws.wz.eId", "ws.ce.id"} and another one for {"ws.wz.eId", "ws.ce2.id"}

It seems to me that the single index is the best choice; however I might be wrong; so I would like to know if there is value in creating the compound index, or any other type.

you could create the indexes, run an explain on your queries and see the effect. — muratgu
– muratgu, Commented Oct 3, 2013 at 23:43

mnemosyn · Accepted Answer · 2013-10-04 18:45:25Z

As muratgu already pointed out, the best way to reason about performance is to stop reasoning and start measuring instead.

However, since measurements can be quite tricky, here's some theory:

You might want to consider one compound index {"ws.wz.eId", "ws.ce1.id"} because that can be used for the 90% case and, for the ten percent case, is equivalent to just having an index on ws.wz.eId.

When you do this, the first query can be matched through the index, the second query will have to find all candidates with matching ws.wz.eId first (fast, index present) and then scan-and-match all candidates to filter out those documents that don't match the ws.ce2.id criterion. Whether that is expensive or not depends on the number of documents with same ws.wz.eId that must be scanned, so this depends very much on your data.

An important factor is the selectivity of the key. For example, if there's a million documents with same ws.wz.eId and only one of those has the ws.ce2.id you're looking for, you might need the index, or want to reverse the query.

Collectives™ on Stack Overflow

compound Index or single index in mongodb

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related