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.