Mongodb - index not being used

Question

I have a collection which contains about 42M documents. I have 2 indexes on this collection, as follows:

{
   "0" : {
      "v" : 1,
      "key" : {
         "_id" : 1
      },
      "name" : "_id_",
      "ns" : "ns.coll1"
   },
   "1" : {
     "v" : 1,
     "unique" : true,
     "key" : {
        "id" : 1
     },
     "name" : "id_1",
     "ns" : "ns.coll1"
   }
}

And, this is how the id field looks like:

"_id" : ObjectId("55f9b6548aefbce6b2fa2fac"),
"id" : {
   "pid" : {
    "f1" : "val1",
    "f2" : "val2"
   }
},

A very simple query on this collection, using the indexed fields, still doesn't use the index.

db.coll1.find({"id.pid.f1":"val1","id.pid.f2":"val2"}).explain();

{
    "cursor" : "BasicCursor",
    "isMultiKey" : false,
    "n" : 1,
    "nscannedObjects" : 42635482,
    "nscanned" : 42635482,
    "nscannedObjectsAllPlans" : 42635482,
    "nscannedAllPlans" : 42635482,
    "scanAndOrder" : false,
    "indexOnly" : false,
    "nYields" : 333089,
    "nChunkSkips" : 0,
    "millis" : 51312,
    "filterSet" : false,
    "stats" : {
        "type" : "COLLSCAN",
        "works" : 42635484,
        "yields" : 333089,
        "unyields" : 333089,
        "invalidates" : 0,
        "advanced" : 1,
        "needTime" : 42635482,
        "needFetch" : 0,
        "isEOF" : 1,
        "docsTested" : 42635482,
        "children" : []
    }
}

What am i missing here? Is doing a full table scan the best approach here, and is that why it isn't using the index? Thanks.

Your index is on the object as a whole, not the sub fields. You would need a compound index on id.pid.f1 and id.pid.f2. Alternately you could search for {id: {pid: {f1: "val1", f2: "val2"}}} — James Wahlin

Rohit Jain Rohit Jain · Accepted Answer · 2015-09-25T05:11:53

you have to create index on nested fields like this

db.index.createIndex({"id.pid.f1":1,"id.pid.f2":1})

Mongodb - index not being used

1 Answers