[SERVER-3368] Use index on array when querying on a a specific position within the array. Created: 05/Jul/11  Updated: 06/Dec/22

Status: Backlog
Project: Core Server
Component/s: Querying
Affects Version/s: None
Fix Version/s: None

Type: Improvement Priority: Major - P3
Reporter: Daniel Crosta Assignee: Backlog - Query Optimization
Resolution: Unresolved Votes: 3
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Assigned Teams:
Query Optimization
Participants:
Case:

 Description   

Multikey arrays are used if you are querying for an array element as:

    db.foo.find({path: 10}).explain()
    {
	"cursor" : "BtreeCursor path_1",
	"nscanned" : 275271,
	"nscannedObjects" : 275271,
	"n" : 275271,
	"millis" : 269,
	"nYields" : 0,
	"nChunkSkips" : 0,
	"isMultiKey" : true,
	"indexOnly" : false,
	"indexBounds" : {
		"path" : [
			[
				10,
				10
			]
		]
	}
    }

But if you want to specifically find an array element at a specific position, the array is not used:

    db.foo.find({"path.0": 10}).explain()
    {
	"cursor" : "BasicCursor",
	"nscanned" : 2194200,
	"nscannedObjects" : 2194200,
	"n" : 80466,
	"millis" : 1685,
	"nYields" : 0,
	"nChunkSkips" : 0,
	"isMultiKey" : false,
	"indexOnly" : false,
	"indexBounds" : {
		
	}
    }

This collection contains about 2.2 million documents with field "path" set to an array of 1 to 5 elements selected at random in range 0-50



 Comments   
Comment by Andrew Ryder (Inactive) [ 16/Dec/14 ]

A search path containing a numeral cannot be assumed to refer to the index of an array.

Given these two documents:

{ "path": { "fruit": "bananas", "0": 10 } }
{ "path": [ 10, "bananas" ] }

Both of these are a valid match for the search {path.0:10}, but only the second one will be found when using the index on "path". Using such an index gives incorrect results.

Generated at Thu Feb 08 03:02:53 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.