|
Explain function show a difference of index's usage with embedded document. nscanned is increase with enbedded document.
Example :
Embedded document
|
db.test.insert({"code" : "000001", doc : {"code1" : "000001", "type" : [ "1" ,"3" ]}});
|
db.test.insert({"code" : "000002", doc : {"code1" : "000001", "type" : [ "1" ]}});
|
db.test.insert({"code" : "000003", doc : {"code1" : "000001", "type" : [ "1", "2"] }});
|
db.test.insert({"code" : "000004", doc : {"code1" : "000001", "type" : [ "2" ]}});
|
db.test.insert({"code" : "000005", doc : {"code1" : "000001", "type" : [ ]}});
|
db.test.insert({"code" : "000006", doc : {"code1" : "000001", "type" : [ "3" ]}});
|
db.test.insert({"code" : "000007", doc : {"code1" : "000001" }});
|
db.test.insert({"code" : "000001", doc : {"code1" : "000002", "type" : [ "1" ,"3" ]}});
|
db.test.insert({"code" : "000002", doc : {"code1" : "000002", "type" : [ "1" ]}});
|
db.test.insert({"code" : "000003", doc : {"code1" : "000002", "type" : [ "1", "2"] }});
|
db.test.insert({"code" : "000004", doc : {"code1" : "000002", "type" : [ "2" ]}});
|
db.test.insert({"code" : "000005", doc : {"code1" : "000002", "type" : [ ]}});
|
db.test.insert({"code" : "000006", doc : {"code1" : "000002", "type" : [ "3" ]}});
|
db.test.insert({"code" : "000007", doc : {"code1" : "000002" }});
|
db.test.insert({"code" : "000001", doc : {"code1" : "000003", "type" : [ "1" ,"3" ]}});
|
db.test.insert({"code" : "000002", doc : {"code1" : "000003", "type" : [ "1" ]}});
|
db.test.insert({"code" : "000003", doc : {"code1" : "000003", "type" : [ "1", "2"] }});
|
db.test.insert({"code" : "000004", doc : {"code1" : "000003", "type" : [ "2" ]}});
|
db.test.insert({"code" : "000005", doc : {"code1" : "000003", "type" : [ ]}});
|
db.test.insert({"code" : "000006", doc : {"code1" : "000003", "type" : [ "3" ]}});
|
db.test.insert({"code" : "000007", doc : {"code1" : "000003" }});
|
|
db.test.ensureIndex( { "doc.code1": 1, "doc.type": -1, "code": -1 }, { unique: false, sparse: false, background: true, name: "index_test"} )
|
|
db.test.find( { "$or":[ { "doc.code1" : "000001" }, { "doc.code1" : "000003" } ], "doc.type" : "2" } ).explain()
|
{
|
"clauses" : [
|
{
|
"cursor" : "BtreeCursor index_test",
|
"isMultiKey" : true,
|
"n" : 2,
|
"nscannedObjects" : 9,
|
"nscanned" : 9,
|
"nscannedObjectsAllPlans" : 9,
|
"nscannedAllPlans" : 9,
|
"scanAndOrder" : false,
|
"indexOnly" : false,
|
"nYields" : 0,
|
"nChunkSkips" : 0,
|
"millis" : 0,
|
"indexBounds" : {
|
"doc.code1" : [
|
[
|
"000001",
|
"000001"
|
]
|
],
|
"doc.type" : [
|
[
|
{
|
"$maxElement" : 1
|
},
|
{
|
"$minElement" : 1
|
}
|
]
|
],
|
"code" : [
|
[
|
{
|
"$maxElement" : 1
|
},
|
{
|
"$minElement" : 1
|
}
|
]
|
]
|
}
|
},
|
{
|
"cursor" : "BtreeCursor index_test",
|
"isMultiKey" : true,
|
"n" : 2,
|
"nscannedObjects" : 9,
|
"nscanned" : 9,
|
"nscannedObjectsAllPlans" : 9,
|
"nscannedAllPlans" : 9,
|
"scanAndOrder" : false,
|
"indexOnly" : false,
|
"nYields" : 0,
|
"nChunkSkips" : 0,
|
"millis" : 0,
|
"indexBounds" : {
|
"doc.code1" : [
|
[
|
"000003",
|
"000003"
|
]
|
],
|
"doc.type" : [
|
[
|
{
|
"$maxElement" : 1
|
},
|
{
|
"$minElement" : 1
|
}
|
]
|
],
|
"code" : [
|
[
|
{
|
"$maxElement" : 1
|
},
|
{
|
"$minElement" : 1
|
}
|
]
|
]
|
}
|
}
|
],
|
"n" : 4,
|
"nscannedObjects" : 18,
|
"nscanned" : 18,
|
"nscannedObjectsAllPlans" : 18,
|
"nscannedAllPlans" : 18,
|
"millis" : 0,
|
"server" : "xxx:27017"
|
}
|
Not embedded document
|
db.test2.insert({"code" : "000001", "code1" : "000001", "type" : [ "1" ,"3" ]});
|
db.test2.insert({"code" : "000002", "code1" : "000001", "type" : [ "1" ]});
|
db.test2.insert({"code" : "000003", "code1" : "000001", "type" : [ "1", "2"] });
|
db.test2.insert({"code" : "000004", "code1" : "000001", "type" : [ "2" ]});
|
db.test2.insert({"code" : "000005", "code1" : "000001", "type" : [ ]});
|
db.test2.insert({"code" : "000006", "code1" : "000001", "type" : [ "3" ]});
|
db.test2.insert({"code" : "000007", "code1" : "000001" });
|
db.test2.insert({"code" : "000001", "code1" : "000002", "type" : [ "1" ,"3" ]});
|
db.test2.insert({"code" : "000002", "code1" : "000002", "type" : [ "1" ]});
|
db.test2.insert({"code" : "000003", "code1" : "000002", "type" : [ "1", "2"] });
|
db.test2.insert({"code" : "000004", "code1" : "000002", "type" : [ "2" ]});
|
db.test2.insert({"code" : "000005", "code1" : "000002", "type" : [ ]});
|
db.test2.insert({"code" : "000006", "code1" : "000002", "type" : [ "3" ]});
|
db.test2.insert({"code" : "000007", "code1" : "000002" });
|
db.test2.insert({"code" : "000001", "code1" : "000003", "type" : [ "1" ,"3" ]});
|
db.test2.insert({"code" : "000002", "code1" : "000003", "type" : [ "1" ]});
|
db.test2.insert({"code" : "000003", "code1" : "000003", "type" : [ "1", "2"] });
|
db.test2.insert({"code" : "000004", "code1" : "000003", "type" : [ "2" ]});
|
db.test2.insert({"code" : "000005", "code1" : "000003", "type" : [ ]});
|
db.test2.insert({"code" : "000006", "code1" : "000003", "type" : [ "3" ]});
|
db.test2.insert({"code" : "000007", "code1" : "000003" });
|
|
db.test2.ensureIndex( { "code1": 1, "type": -1, "code": -1 }, { unique: false, sparse: false, background: true, name: "index_test2"} )
|
|
db.test2.find( { "$or":[ { "code1" : "000001" }, { "code1" : "000003" } ], "type" : "2" } ).explain()
|
{
|
"clauses" : [
|
{
|
"cursor" : "BtreeCursor index_test2",
|
"isMultiKey" : true,
|
"n" : 2,
|
"nscannedObjects" : 2,
|
"nscanned" : 2,
|
"nscannedObjectsAllPlans" : 2,
|
"nscannedAllPlans" : 2,
|
"scanAndOrder" : false,
|
"indexOnly" : false,
|
"nYields" : 0,
|
"nChunkSkips" : 0,
|
"millis" : 0,
|
"indexBounds" : {
|
"code1" : [
|
[
|
"000001",
|
"000001"
|
]
|
],
|
"type" : [
|
[
|
"2",
|
"2"
|
]
|
],
|
"code" : [
|
[
|
{
|
"$maxElement" : 1
|
},
|
{
|
"$minElement" : 1
|
}
|
]
|
]
|
}
|
},
|
{
|
"cursor" : "BtreeCursor index_test2",
|
"isMultiKey" : true,
|
"n" : 2,
|
"nscannedObjects" : 2,
|
"nscanned" : 2,
|
"nscannedObjectsAllPlans" : 2,
|
"nscannedAllPlans" : 2,
|
"scanAndOrder" : false,
|
"indexOnly" : false,
|
"nYields" : 0,
|
"nChunkSkips" : 0,
|
"millis" : 0,
|
"indexBounds" : {
|
"code1" : [
|
[
|
"000003",
|
"000003"
|
]
|
],
|
"type" : [
|
[
|
"2",
|
"2"
|
]
|
],
|
"code" : [
|
[
|
{
|
"$maxElement" : 1
|
},
|
{
|
"$minElement" : 1
|
}
|
]
|
]
|
}
|
}
|
],
|
"n" : 4,
|
"nscannedObjects" : 4,
|
"nscanned" : 4,
|
"nscannedObjectsAllPlans" : 4,
|
"nscannedAllPlans" : 4,
|
"millis" : 0,
|
"server" : "xxx:27017"
|
}
|
|