hi,
i want to create a compound index for sort and range filter,
i dont know why its doing a seperate filter and not using the compound index for the range filter.
here is my data:
{
"_id" : ObjectId("6080a5c299aecc30a333dfc7"),
"name" : "Shakir",
"location" : "Ottawa",
"region" : "AMER",
"joined" : 2015
}
{
"_id" : ObjectId("6080a5c299aecc30a333dfc8"),
"name" : "Chris",
"location" : "Austin",
"region" : "AMER",
"joined" : 2016
}
{
"_id" : ObjectId("6080a5c299aecc30a333dfc9"),
"name" : "III",
"location" : "Sydney",
"region" : "APAC",
"joined" : 2016
}
{
"_id" : ObjectId("6080a5c299aecc30a333dfca"),
"name" : "Miguel",
"location" : "Barcelona",
"region" : "EMEA",
"joined" : 2017
}
{
"_id" : ObjectId("6080a5c299aecc30a333dfcb"),
"name" : "Alex",
"location" : "Toronto",
"region" : "AMER",
"joined" : 2018
}
and i created this index:
db.getCollection("region").createIndex({ "region" : 1, "joined" : 1})
this is my query:
db.getCollection("region").find({ joined: { $gt: 2015 } }).sort({ region: 1, })
here is my winning plan:
"winningPlan" : {
"stage" : "FETCH",
"filter" : {
"joined" : {
"$gt" : 2015.0
}
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"region" : 1.0,
"joined" : 1.0
},
"indexName" : "region_1_joined_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"region" : [
],
"joined" : [
]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"region" : [
"[MinKey, MaxKey]"
],
"joined" : [
"[MinKey, MaxKey]"
]
}
}
}
thanks