CODEWITHSUNDEEP
mongodbmongodb-query
Avij
Avij

Reputation: 694

index usage in mongodb when index key is repetitive

I have a collection that has repetitive value as below

{ "_id" : ObjectId("59f42f500790826aae869517"), "student_id" : 1, "type" : "exam", "score" : 38.09994424551998 }
{ "_id" : ObjectId("59f42f500790826aae869518"), "student_id" : 1, "type" : "homework", "score" : 91.98453327244025 }
{ "_id" : ObjectId("59f42f500790826aae869519"), "student_id" : 1, "type" : "quiz", "score" : 51.50397276737756 }

Now i have made 'student_id' as an index and there are indexes with

'student_id,type' , 'student_id,score' & 'student_id,type,score'

. When i run below query on this

db.scores.explain("executionStats").find({'student_id':{$gte:10}},{_id:0,type:0,score:0})

I expect that only 'student_id' index should have been used as all other fields are projected 0 so i do not need them. However it tells me that the used index is 'student_id,type'. One guess i could take it that mongodb would scan each doc for a particular student_id to know the document differentiation and the best possible way would be to use "student_id,type" INDEX. However i could be wrong. So please help me to know the reason. Thanks.

Upvotes: 0

Views: 47

Answers (1)

dnickless
dnickless

Reputation: 10918

I just experimented with your scenario and can confirm that the order of the index creation seems to be the key here.

The explanation for that is probably that the query optimizer just looks for an index that covers the required fields (only student_id in your case). So this "find the right" index algorithm goes through the list of available indexes in the order of creation. And since the first index that it encounters (apart from the default one on the _id field...) already fits your query the optimizer decides to use it and doesn't bother looking any further.

The documentation on indexes states:

MongoDB indexes use a B-tree data structure.

and for compound indexes:

The order of fields listed in a compound index has significance. For instance, if a compound index consists of { userid: 1, score: -1 }, the index sorts first by userid and then, within each userid value, sorts by score.

So since all your indexes have student_id as their first field, all indexes will look identical in the top part of the tree ("right below the root") and serve your query equally well.

Upvotes: 1

Related Questions