Uploaded image for project: 'Core Server'
  1. Core Server
  2. SERVER-7354

Index with enbedded document

      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"
      }
      

            Assignee:
            aaron Aaron Staple
            Reporter:
            lucas.pouzac.pro Lucas Pouzac
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

              Created:
              Updated:
              Resolved: