I have a collection with one 4 key compound index:
> db.event.getIndexes()
[
{
"v" : 2,
"key" : {
"_id" : 1
},
"name" : "_id_",
},
{
"v" : 2,
"key" : {
"epochWID" : 1,
"category" : 1,
"mos.types" : 1,
"mos.name" : 1
},
"name" : "epochWID_category_motype_movalue",
}
]
Query is as follows:
> db.event.explain().find({ "epochWID": 1510456188087, "category": 6, "mos.types": 9, "mos.name": "ctx_1" })
{
"queryPlanner" : {
"plannerVersion" : 1,
"indexFilterSet" : false,
"parsedQuery" : {
"$and" : [
{
"category" : {
"$eq" : 6
}
},
{
"epochWID" : {
"$eq" : 1510456188087
}
},
{
"mos.name" : {
"$eq" : "ctx_1"
}
},
{
"mos.types" : {
"$eq" : 9
}
}
]
},
"winningPlan" : {
"stage" : "FETCH",
"filter" : {
"mos.name" : {
"$eq" : "ctx_1"
}
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"epochWID" : 1,
"category" : 1,
"mos.types" : 1,
"mos.name" : 1
},
"indexName" : "epochWID_category_motype_movalue",
"isMultiKey" : true,
"multiKeyPaths" : {
"epochWID" : [ ],
"category" : [ ],
"mos.types" : [
"mos",
"mos.types"
],
"mos.name" : [
"mos"
]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"epochWID" : [
"[1510456188087.0, 1510456188087.0]"
],
"category" : [
"[6.0, 6.0]"
],
"mos.types" : [
"[9.0, 9.0]"
],
"mos.name" : [
"[MinKey, MaxKey]"
]
}
}
},
"rejectedPlans" : [ ]
},
"serverInfo" : {
"version" : "3.4.9",
},
"ok" : 1
}
Now if you look at the plan's indexBounds: it uses the first 3 keys but not the 4th mos.name, why?
"indexBounds" : {
"epochWID" : [
"[1510456188087.0, 1510456188087.0]"
],
"category" : [
"[6.0, 6.0]"
],
"mos.types" : [
"[9.0, 9.0]"
],
"mos.name" : [
"[MinKey, MaxKey]"
]
}
Based on https://docs.mongodb.com/manual/core/index-multikey/#compound-multikey-indexes we need to use $elemMatch, so following query uses the full index
> db.event.explain().find({ "epochWID": 1510456188087, "category": 6, "mos": { $elemMatch: {"types": 9, "name": "ctx_1"} } })
{
"queryPlanner" : {
"plannerVersion" : 1,
"indexFilterSet" : false,
"parsedQuery" : {
"$and" : [
{
"mos" : {
"$elemMatch" : {
"$and" : [
{
"name" : {
"$eq" : "ctx_1"
}
},
{
"types" : {
"$eq" : 9
}
}
]
}
}
},
{
"category" : {
"$eq" : 6
}
},
{
"epochWID" : {
"$eq" : 1510456188087
}
}
]
},
"winningPlan" : {
"stage" : "FETCH",
"filter" : {
"mos" : {
"$elemMatch" : {
"$and" : [
{
"types" : {
"$eq" : 9
}
},
{
"name" : {
"$eq" : "ctx_1"
}
}
]
}
}
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"epochWID" : 1,
"category" : 1,
"mos.types" : 1,
"mos.name" : 1
},
"indexName" : "epochWID_category_motype_movalue",
"isMultiKey" : true,
"multiKeyPaths" : {
"epochWID" : [ ],
"category" : [ ],
"mos.types" : [
"mos",
"mos.types"
],
"mos.name" : [
"mos"
]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"epochWID" : [
"[1510456188087.0, 1510456188087.0]"
],
"category" : [
"[6.0, 6.0]"
],
"mos.types" : [
"[9.0, 9.0]"
],
"mos.name" : [
"[\"ctx_1\", \"ctx_1\"]"
]
}
}
},
"rejectedPlans" : [ ]
},
"serverInfo" : {
"version" : "3.4.9",
},
"ok" : 1
}
EDIT: I contacted MongoDb support. Regarding multi key indexes and array fields - tl;dr is - An index is fine as long as only one of the indexed fields ever contains an array value (which is true in my case). Nesting level doesn't matter. The problem is indeed parallel arrays due to need of cartesian product.