Multikey index sort bounds
Parent: MongoDB Query Shape Optimization: Sort, Pagination, Operators and Sharded Targeting · Published reference · snapshot 2026-10-23 · skill mongodb-cohort-4d4f831f8d8c-pending
↓ Facts as markdownall context files
Source-anchored research for Multikey index sort bounds. Run status: COMPLETED.
These notes link each claim to its source. Some citations identify archived research reports that are no longer hosted on this site. A published reference means the content is available; it does not certify independent review or accuracy.Read the editorial policy and follow the sources before relying on a claim.
Definitions
- Tighter multikey filter bounds are not evidence that a sort is index-provided. For example, $elemMatch can intersect two array predicates into narrower bounds, but an array sort must still satisfy the separate full-bounds and path-prefix restrictions. This is an inference from the documented filtering and sorting rules. (medium confidence) [source]
How it works
- Full bounds on a.c do not by themselves permit an index-provided sort when a is an array: bounding a.b in index {'a.b':1,'a.c':1} can hide the a.c element that should determine order. SERVER-31898 explains this shared-prefix constraint; SERVER-32525 supplies an older reproduction of index-dependent ordering. (high confidence) [source]
How-to and procedures
- To investigate multikey sort performance, inspect how the actual query plan uses the index and check whether a separate sort remains. Index use for document selection alone does not establish index-provided ordering. This verification method follows from explain's purpose and the documented multikey exception. (medium confidence) [source]
Problems, failure modes and limitations
- A Stack Overflow question reports that `find({}, { _id: 1 }).sort({teamIds: 1}).limit(25)` examined 52,320 keys and 51,412 documents beneath a `SORT` stage on a MongoDB 3.6.8 primary, while the author reports 25 keys and documents on a 3.6.7 secondary. This is a version-specific user report, not a prediction for current MongoDB. (low confidence) [source]
- An October 2019 Stack Overflow report describes a sharded MongoDB 3.6.3 collection with a multikey index on `transfers.amount` and a descending sort on that subfield; the user reports an error stating that the sort used more than 33554432 bytes of RAM. Treat it as an unreplicated historical symptom, not a current memory limit or proof that all multikey sorts block. (low confidence) [source]
Comparisons and alternatives
- To assess an array sort, inspect indexBounds and multiKeyPaths alongside SORT and execution counters rather than treating IXSCAN or limit() as proof that sorting is cheap. A plan can use an index and still perform a blocking sort; the historical teamIds example demonstrates exactly that shape. (medium confidence) [source]
Facts and statements
- For sorting on an array field indexed by a multikey index, MongoDB documents a separate in-memory sort unless both conditions hold: all sort fields have [MinKey, MaxKey] index bounds, and no bounded multikey-indexed field shares a path prefix with the sort pattern. These conditions supplement normal index sort eligibility; they do not guarantee the planner chooses that index. (high confidence) [source]
- Ascending array sorts use the lowest array element; descending sorts use the highest. A predicate restricting matching array elements does not redefine the document's sort key. SERVER-31898 illustrates why scanning only myArray >= 5 would misorder arrays [1,6,7] and [3,4,5]. (high confidence) [source]
- Changing independent array comparisons to $elemMatch changes query meaning as well as enabling bound intersection. Codevra's worked example contrasts scores [70,100] with [92]: independent >=90 and <=95 predicates match both documents, while the same-element condition matches only [92]. Treat this as a semantic decision before optimizing an array filter that precedes sorting. (high confidence) [source]
- A multikey index creates separate entries for distinct array values, so one document can have multiple index entries. For array sorting, MongoDB generates the set of keys for the sort pattern and uses the minimum key by index ordering as the document-level sort key; an individual entry is therefore not generally the document's sort key. (medium confidence) [source]
- For a single array sort field, ascending order uses its lowest-valued element and descending order its highest-valued element. The SERVER-19402 change replaced earlier find behavior that selected an in-bounds element using the query predicate; restricting array keys can therefore omit the value needed for the document's sort order. (low confidence) [source]
Children
- No children recorded.