-
Type: Improvement
-
Resolution: Done
-
Priority: Major - P3
-
None
-
Affects Version/s: 2.2.0
-
Component/s: Index Maintenance, Performance
-
Environment:Ubuntu 12.04 LTS
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" }
- related to
-
SERVER-7509 Combine index ranges for dotted fields within a $elemMatch expression, when possible
- Closed