Use dotted indexes for embedded document queries

XMLWordPrintableJSON

    • Type: Improvement
    • Resolution: Unresolved
    • Priority: Major - P3
    • None
    • Affects Version/s: None
    • Component/s: Index Maintenance
    • Query Optimization
    • None
    • None
    • None
    • None
    • None
    • None
    • None

      Setup:

      > db.users.save({first_name:"Foo", ext_id :{ id:"6708526",type:2}});
> db.users.ensureIndex({'ext_id.id':1,'ext_id.type':1})

      Using the dot-notation, the explain plan shows that the index is used:

      > db.users.find({"ext_id.id":"6708526", "ext_id.type":2}).explain()
{
	"cursor" : "BtreeCursor ext_id.id_1_ext_id.type_1",
	"nscanned" : 1,
	"nscannedObjects" : 1,
	"n" : 1,
	"millis" : 0,
	"nYields" : 0,
	"nChunkSkips" : 0,
	"isMultiKey" : false,
	"indexOnly" : false,
	"indexBounds" : {
		"ext_id.id" : [
			[
				"6708526",
				"6708526"
			]
		],
		"ext_id.type" : [
			[
				2,
				2
			]
		]
	}
}

      However, expressing the query with a document results in the index not being used:

      > db.users.find({'ext_id':{'id':"6708526", 'type':2}}).explain()
{
	"cursor" : "BasicCursor",
	"nscanned" : 1,
	"nscannedObjects" : 1,
	"n" : 1,
	"millis" : 0,
	"nYields" : 0,
	"nChunkSkips" : 0,
	"isMultiKey" : false,
	"indexOnly" : false,
	"indexBounds" : {
		
	}
}

      If there are indexed fields representing any of embedded fields in the query, in dot-notation, they should be used. This can be done to filter out the documents needed before the exact embedded document matching is done for the actual query criteria.

            Assignee:
            [DO NOT USE] Backlog - Query Optimization
            Reporter:
            Alvin Richards (Inactive)
            Votes:
            3 Vote for this issue
            Watchers:
            6 Start watching this issue

              Created:
              Updated: