Show
created the non-sparse index _
{shopId:1, missingSince:1}
_
db.offer.createIndex({shopId:1, missingSince:1})
insert the following documents:
db.offer.insert({ "_id" : 1, "v" : 1 });
db.offer.insert({ "_id" : 2, "v" : 1 });
db.offer.insert({ "_id" : 3, "v" : 1 });
db.offer.insert({ "_id" : 4, "shopId" : 1, "v" : 1 });
db.offer.insert({ "_id" : 5, "shopId" : 1, "v" : 1 });
db.offer.insert({ "_id" : 6, "shopId" : 1, "v" : 1 });
db.offer.insert({ "_id" : 7, "shopId" : 1, "v" : 1 });
db.offer.insert({ "_id" : 8, "shopId" : 1, "v" : 1 });
db.offer.insert({ "_id" : 9, "shopId" : 1, "missingSince" : null , "v" : 1 });
db.offer.insert({ "_id" : 10, "shopId" : 1, "missingSince" : null , "v" : 1 });
db.offer.insert({ "_id" : 11, "shopId" : 1, "missingSince" : null , "v" : 1 });
db.offer.insert({ "_id" : 12, "shopId" : 1, "missingSince" : null , "v" : 1 });
db.offer.insert({ "_id" : 13, "shopId" : 1, "missingSince" : null , "v" : 1 });
db.offer.insert({ "_id" : 14, "shopId" : 1, "missingSince" : null , "v" : 1 });
db.offer.insert({ "_id" : 15, "shopId" : 1, "missingSince" : null , "v" : 1 });
db.offer.insert({ "_id" : 16, "shopId" : 1, "missingSince" : ISODate( "2017-05-22T07:52:40.831Z" ), "v" : 1 });
db.offer.insert({ "_id" : 17, "shopId" : 1, "missingSince" : ISODate( "2017-05-22T07:52:40.831Z" ), "v" : 1 });
db.offer.insert({ "_id" : 18, "shopId" : 1, "missingSince" : ISODate( "2017-05-22T07:52:40.831Z" ), "v" : 1 });
db.offer.insert({ "_id" : 19, "shopId" : 1, "missingSince" : ISODate( "2017-05-22T07:52:40.831Z" ), "v" : 1 });
db.offer.insert({ "_id" : 20, "shopId" : 1, "missingSince" : ISODate( "2017-05-22T07:52:40.831Z" ), "v" : 1 });
db.offer.insert({ "_id" : 21, "shopId" : 1, "missingSince" : ISODate( "2017-05-22T07:52:40.831Z" ), "v" : 1 });
db.offer.insert({ "_id" : 22, "shopId" : 1, "missingSince" : ISODate( "2017-05-22T07:52:40.831Z" ), "v" : 1 });
db.offer.insert({ "_id" : 23, "shopId" : 1, "missingSince" : ISODate( "2017-05-22T07:52:40.831Z" ), "v" : 1 });
db.offer.insert({ "_id" : 24, "shopId" : 1, "missingSince" : ISODate( "2017-05-22T07:52:40.831Z" ), "v" : 1 });
db.offer.insert({ "_id" : 25, "shopId" : 1, "missingSince" : ISODate( "2017-05-22T07:52:40.831Z" ), "v" : 1 });
db.offer.insert({ "_id" : 26, "shopId" : 1, "missingSince" : ISODate( "2017-05-22T07:52:40.831Z" ), "v" : 1 });
db.offer.insert({ "_id" : 27, "shopId" : 1, "missingSince" : ISODate( "2017-05-22T07:52:40.831Z" ), "v" : 1 });
db.offer.insert({ "_id" : 28, "shopId" : 1, "missingSince" : ISODate( "2017-05-22T07:52:40.831Z" ), "v" : 1 });
execute explain(true) for the following query:
db.offer.explain( true ).count({ "shopId" :1, "missingSince" : null })
the execution stats shows that 12 documents needed to be examined, meaning that the query was not covered by the index
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "test.offer" ,
"indexFilterSet" : false ,
"parsedQuery" : {
"$and" : [
{
"missingSince" : {
"$eq" : null
}
},
{
"shopId" : {
"$eq" : 1
}
}
]
},
"winningPlan" : {
"stage" : "COUNT" ,
"inputStage" : {
"stage" : "FETCH" ,
"filter" : {
"missingSince" : {
"$eq" : null
}
},
"inputStage" : {
"stage" : "IXSCAN" ,
"keyPattern" : {
"shopId" : 1,
"missingSince" : 1
},
"indexName" : "shopId_1_missingSince_1" ,
"isMultiKey" : false ,
"isUnique" : false ,
"isSparse" : false ,
"isPartial" : false ,
"indexVersion" : 1,
"direction" : "forward" ,
"indexBounds" : {
"shopId" : [
"[1.0, 1.0]"
],
"missingSince" : [
"[ null , null ]"
]
}
}
}
},
"rejectedPlans" : [ ]
},
"executionStats" : {
"executionSuccess" : true ,
"nReturned" : 0,
"executionTimeMillis" : 0,
"totalKeysExamined" : 12,
"totalDocsExamined" : 12,
"executionStages" : {
"stage" : "COUNT" ,
"nReturned" : 0,
"executionTimeMillisEstimate" : 0,
"works" : 13,
"advanced" : 0,
"needTime" : 12,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"invalidates" : 0,
"nCounted" : 12,
"nSkipped" : 0,
"inputStage" : {
"stage" : "FETCH" ,
"filter" : {
"missingSince" : {
"$eq" : null
}
},
"nReturned" : 12,
"executionTimeMillisEstimate" : 0,
"works" : 13,
"advanced" : 12,
"needTime" : 0,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"invalidates" : 0,
"docsExamined" : 12,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN" ,
"nReturned" : 12,
"executionTimeMillisEstimate" : 0,
"works" : 13,
"advanced" : 12,
"needTime" : 0,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"invalidates" : 0,
"keyPattern" : {
"shopId" : 1,
"missingSince" : 1
},
"indexName" : "shopId_1_missingSince_1" ,
"isMultiKey" : false ,
"isUnique" : false ,
"isSparse" : false ,
"isPartial" : false ,
"indexVersion" : 1,
"direction" : "forward" ,
"indexBounds" : {
"shopId" : [
"[1.0, 1.0]"
],
"missingSince" : [
"[ null , null ]"
]
},
"keysExamined" : 12,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0
}
}
},
"allPlansExecution" : [ ]
},
"serverInfo" : {
"host" : "Kays MacBook Pro" ,
"port" : 27017,
"version" : "3.2.6" ,
"gitVersion" : "05552b562c7a0b3143a729aaa0838e558dc49b25"
},
"ok" : 1
}