mongodb index range find

Question

if I have these docs:

{"name": 1, "score": 2}
{"name": 1, "score": 4}
{"name": 2, "score": 2}
{"name": 2, "score": 4}

if I ensure the index:

db.test.ensureIndex({"name":1, "score":1})

then I try to find():

db.test.find({"score": 4})

I use explain(), and found that this query can not use the index and it scan all the four docs.

I wonder why it scans all the docs?

you know, if I enum all the "name" 's value (1 and 2):

db.test.find({"$or":["name":1, "name":2], "score":4})

it can use the index and only scanned two docs.

why mongodb can not do this thing for me?

you shouldn't use $or when both clauses have the same field. Use { name : { $in : [ 1, 2 ] } } instead. — Asya Kamsky
– Asya Kamsky, Commented Feb 6, 2014 at 6:14

Neil Lunn · Accepted Answer · 2014-02-06 07:05:05Z

1

The reason your index is not being used is because $or is an exclusion operator, and that means it more or less scans everything in order to determine what is not a match.

What you really want to use is $in. This will reduce the match to only those values that are contained in the set.

db.test.find({"$in":["name":1, "name":2], "score":4})

As this is inclusive that match can be applied to the index without doing a full scan.

answered Feb 6, 2014 at 7:05

Neil Lunn

151k36 gold badges356 silver badges327 bronze badges

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

Comments

abhishekmahawar · Accepted Answer · 2014-02-06 09:32:18Z

In mongoDB, if there's a compound index on the collection(like the case in your collection, compound index is (name, score)), the order of indexed fields is very important. MongoDB can use the compound index only for queries that include the prefix of the index fields. So, in your case, the index will be only for queries that include :
1) name field
2) name and score field.

For any other queries, index won't be used. This is because of the way mongoDB creates compound indexes. The documents will be first indexed by name field and then by score field. If a query includes only score field, there's no way mongoDB can use the index for finding the documents.

Reference : http://docs.mongodb.org/manual/core/index-compound/#compound-index-prefix

Sergio Tulentsev · Accepted Answer · 2014-02-06 09:35:28Z

0

General rule of thumb is this: If you have a composite index and you search by a field from it, you also have to specify all the fields to the left of it in the index definition.

You can think of composite indexes this way: documents are indexed/grouped by a first field and then, within these groups, they are indexed by a second field.

So, when you attempt to search by a second field, not specifying the first one, mongo will have to scan the entire index, because it can't efficiently exclude items from traversing.

db.test.find({"name": {"$in": [1, 2]}, "score": 4})

In this case, it will only look at name=1 and name=2 index entries and look for score=4 there. If you don't specify name, it will scan everything. It's not very efficient.

edited Feb 6, 2014 at 9:35

answered Feb 6, 2014 at 6:09

Sergio Tulentsev

231k43 gold badges381 silver badges373 bronze badges

Collectives™ on Stack Overflow

mongodb index range find

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related