CODEWITHSUNDEEP
mongodbindexingmongodb-query
user2848932
user2848932

Reputation: 786

mongodb index range find

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?

Upvotes: 0

Views: 236

Answers (3)

Sergio Tulentsev
Sergio Tulentsev

Reputation: 230306

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.

Upvotes: 0

abhishekmahawar
abhishekmahawar

Reputation: 229

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

Upvotes: 0

Neil Lunn
Neil Lunn

Reputation: 151102

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.

Upvotes: 1

Related Questions