Multikey indexes and index-provided sort order
Parent: SORT_MERGE and $in sort expansion · Published reference · snapshot 2026-10-31
↓ Facts as markdownall context files
Source-anchored research for Multikey indexes and index-provided sort order. 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.
How it works
- A predicate on a.b can invalidate index-provided sorting on a.c when both traverse array a, even if a.c itself has full bounds: restricting a.b can exclude the a.c element that should determine document order. SERVER-32525 reproduces index-dependent ordering, and SERVER-31898 explains the shared-prefix restriction. (high confidence) [source]
How-to and procedures
- To assess whether ordering comes from the index, inspect the actual plan for a separate SORT together with indexBounds and multiKeyPaths. IXSCAN alone proves neither absence of blocking sorting nor low work: the published array example shows both an unbounded scan without SORT and a bounded scan beneath SORT. (medium confidence) [source]
Problems, failure modes and limitations
- A multikey index can still supply scalar-field ordering: the illustrated index {"children.child_value":1,parent_value:1} supports an equality predicate on children.child_value followed by sorting on scalar parent_value, without an additional sort. This scopes any blanket claim that multikey indexes cannot provide sort order. (medium confidence) [source]
- SERVER-33387 records a MongoDB 3.6.2 report of a 33554432-byte sort-memory failure despite a matching compound index definition. Treat this as historical evidence, not a current memory limit or proof that every multikey sort fails. (low confidence) [source]
Facts and statements
- MongoDB automatically treats an index over an array-valued field as multikey. Distinct array values generate index entries that can point to the same document; duplicate values within that document do not require duplicate entries. (high confidence) [source]
- For sorting an indexed array field, MongoDB documents a separate blocking sort unless every sort field has [MinKey, MaxKey] bounds and no bounded multikey field shares a path prefix with the sort pattern. These eligibility conditions supplement ordinary index sort requirements; they do not promise planner selection. (high confidence) [source]
- Ascending array sorts use the lowest-valued array element, and descending sorts use the highest. The matching predicate does not redefine that document sort key to include only matching elements. (high confidence) [source]
- It is incorrect to reject all multikey indexes as incapable of supplying sort order. The documented full-bounds exceptions and a published unfiltered array-sort explain example show that an index scan can supply order without a blocking SORT. (high confidence) [source]
- Compound index sorting requires sort keys in index order and directions matching the index pattern or its complete inverse. Sorting on a non-prefix subset requires equality conditions on all preceding index keys; these general rules must be considered alongside the multikey restrictions. (medium confidence) [source]
- Inference for operation rejection filters: multikey status or the presence of an index is insufficient to classify an operation as requiring a blocking sort. First determine ordering from its actual bounds, shared array paths, and plan; then apply the intended rejection policy. (medium confidence) [source]
- If the application wants to sort only by matching array elements, MongoDB recommends creating a field containing the matched values, for example with $filter and $addFields, and sorting that field. (high confidence) [source]
- Inspect explain() for a SORT stage to determine whether MongoDB performs an explicit sort. The presence of an index, or its use for filtering, alone does not establish that the index supplies the requested order. (medium confidence) [source]
Children
- Multikey index bounds and shared array paths (frontier)
- Array sort-key semantics (frontier)
- Explain plan detection of blocking sorts (frontier)
Frontier under this node: Array sort-key semantics, Explain plan detection of blocking sorts, Multikey index bounds and shared array paths