Show
1) create a testindex collection
2) insert some documents
db.testindex.insert([
{
"user" : "mario",
"removed" : false,
"objId" : "299939",
"p" : {
"id" : "content1"
},
"a" : "attributea",
"b" : "attributeb",
"c" : "attributec"
},
{
"user" : "mario",
"removed" : false,
"objId" : "299938",
"p" : {
"id" : "content2"
},
"a" : "attributea",
"b" : "attributeb",
"c" : "attributec"
},
{
"user" : "mario",
"removed" : false,
"objId" : "1129229",
"p" : {
"id" : "content3"
},
"a" : "attributea",
"b" : "attributeb",
"c" : "attributec"
},
{
"user" : "mario",
"removed" : false,
"objId" : "6600954",
"p" : {
"id" : "content4"
},
"a" : "attributea",
"b" : "attributeb",
"c" : "attributec"
},
{
"user" : "mario",
"removed" : false,
"objId" : "8889998",
"p" : {
"id" : "content5"
},
"a" : "attributea",
"b" : "attributeb",
"c" : "attributec"
}]
)
3) create some indexes
//generic ingex on collection
db.testindex.ensureIndex({ "user" : 1,"removed":1,"_id" : 1 });
// index over the objId
db.testindex.ensureIndex({ "user" : 1,"removed":1,"objId" : 1 });
//index over p.id
db.testindex.ensureIndex({ "user" : 1,"removed":1,"p.id" : 1 });
4) optimized query
db.testindex.find({"user":"mario","removed":false, "$or" : [ { "objId" : "299939"} , { "p.id" : "543534"}]}) // the query use the two indexes user_1_removed_1_objId_1 and user_1_removed_1_p.id_1
// explain result
{
"clauses" : [
{
"cursor" : "BtreeCursor user_1_removed_1_objId_1",
"isMultiKey" : false,
"n" : 1,
"nscannedObjects" : 1,
"nscanned" : 1,
"nscannedObjectsAllPlans" : 1,
"nscannedAllPlans" : 1,
"scanAndOrder" : false,
"indexOnly" : false,
"nYields" : 0,
"nChunkSkips" : 0,
"millis" : 0,
"indexBounds" : {
"user" : [
[
"mario",
"mario"
]
],
"removed" : [
[
false,
false
]
],
"objId" : [
[
"299939",
"299939"
]
]
}
},
{
"cursor" : "BtreeCursor user_1_removed_1_p.id_1",
"isMultiKey" : false,
"n" : 0,
"nscannedObjects" : 0,
"nscanned" : 0,
"nscannedObjectsAllPlans" : 0,
"nscannedAllPlans" : 0,
"scanAndOrder" : false,
"indexOnly" : false,
"nYields" : 0,
"nChunkSkips" : 0,
"millis" : 0,
"indexBounds" : {
"user" : [
[
"mario",
"mario"
]
],
"removed" : [
[
false,
false
]
],
"p.id" : [
[
"543534",
"543534"
]
]
}
}
],
"n" : 1,
"nscannedObjects" : 1,
"nscanned" : 1,
"nscannedObjectsAllPlans" : 1,
"nscannedAllPlans" : 1,
"millis" : 0,
}
5) Incapsulate the or inside an other $or or $and
db.testindex.find({
"user":"mario","removed":false,
"$and" : [ {
"$or" : [ { "objId" : "299939"} , { "p.id" : "543534"}]}
]}
).explain()
{
"cursor" : "BtreeCursor user_1_removed_1__id_1",
"isMultiKey" : false,
"n" : 1,
"nscannedObjects" : 5,
"nscanned" : 5,
"nscannedObjectsAllPlans" : 5,
"nscannedAllPlans" : 5,
"scanAndOrder" : false,
"indexOnly" : false,
"nYields" : 0,
"nChunkSkips" : 0,
"millis" : 0,
"indexBounds" : {
"user" : [
[
"mario",
"mario"
]
],
"removed" : [
[
false,
false
]
],
"_id" : [
[
{
"$minElement" : 1
},
{
"$maxElement" : 1
}
]
]
},
}
Mongodb is not able to rewrite the nested $or into the canonical rooted $or form, thus can't make use of the index union.