I have added created a collection first
and created index
db.first.createIndex({a:1, b:1, c:1, d:1, e:1, f:1});
and inserted data
db.first.insert({a:1, b:2, c:3, d:4, e:5, f:6});
db.first.insert({a:1, b:6});
When making queries like
db.first.find({f: 6, a:1, c:3}).sort({b: -1}).explain();
index are used
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "myproject.first",
"indexFilterSet" : false,
"parsedQuery" : {
"$and" : [
{
"a" : {
"$eq" : 1
}
},
{
"c" : {
"$eq" : 3
}
},
{
"f" : {
"$eq" : 6
}
}
]
},
"winningPlan" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"a" : 1,
"b" : 1,
"c" : 1,
"d" : 1,
"e" : 1,
"f" : 1
},
"indexName" : "a_1_b_1_c_1_d_1_e_1_f_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"a" : [ ],
"b" : [ ],
"c" : [ ],
"d" : [ ],
"e" : [ ],
"f" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "backward",
"indexBounds" : {
"a" : [
"[1.0, 1.0]"
],
"b" : [
"[MaxKey, MinKey]"
],
"c" : [
"[3.0, 3.0]"
],
"d" : [
"[MaxKey, MinKey]"
],
"e" : [
"[MaxKey, MinKey]"
],
"f" : [
"[6.0, 6.0]"
]
}
}
},
"rejectedPlans" : [
{
"stage" : "SORT",
"sortPattern" : {
"b" : -1
},
"inputStage" : {
"stage" : "SORT_KEY_GENERATOR",
"inputStage" : {
"stage" : "FETCH",
"filter" : {
"$and" : [
{
"c" : {
"$eq" : 3
}
},
{
"f" : {
"$eq" : 6
}
}
]
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"a" : 1
},
"indexName" : "a_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"a" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"a" : [
"[1.0, 1.0]"
]
}
}
}
}
}
]
},
"serverInfo" : {
"host" : "Manishs-MacBook-Pro.local",
"port" : 27017,
"version" : "3.6.4",
"gitVersion" : "d0181a711f7e7f39e60b5aeb1dc7097bf6ae5856"
},
"ok" : 1
}
but when I use or query
db.first.find({ $or: [{f: 6}, {a:1}]}).explain();
index is not used instead columns are scanned
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "myproject.first",
"indexFilterSet" : false,
"parsedQuery" : {
"$or" : [
{
"a" : {
"$eq" : 1
}
},
{
"f" : {
"$eq" : 6
}
}
]
},
"winningPlan" : {
"stage" : "SUBPLAN",
"inputStage" : {
"stage" : "COLLSCAN",
"filter" : {
"$or" : [
{
"a" : {
"$eq" : 1
}
},
{
"f" : {
"$eq" : 6
}
}
]
},
"direction" : "forward"
}
},
"rejectedPlans" : [ ]
},
"serverInfo" : {
"host" : "Manishs-MacBook-Pro.local",
"port" : 27017,
"version" : "3.6.4",
"gitVersion" : "d0181a711f7e7f39e60b5aeb1dc7097bf6ae5856"
},
"ok" : 1
}
Please let me know if I am doing something wrong.
Best Answer
The issue here is, you've created a compound index on {a:1, b:1, c:1, d:1, e:1, f:1} fields but you're not following the order of the index. So your queries should contain all the fields in the same order that you've constructed your index. Since the field 'f' is in the tail end of the index, your queries will not utilize or even identify it
Your queries:
To make both your above queries use the index, you should build the compound index as below: