Compound-index prefix and sort-direction compatibility
Parent: Collation and index sort compatibility · Published reference · snapshot 2026-10-31
↓ Facts as markdownall context files
Source-anchored research for Compound-index prefix and sort-direction compatibility. 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.
Structure and components
- A compound-index prefix consists of consecutive fields starting at the first index field. A sort matching such a prefix can use its ordering; a filter using the index does not by itself establish sort compatibility. (high confidence) [source]
How it works
- A non-prefix sort subset can use index order when all preceding index fields have equality predicates. With {a:1,b:1,c:1}, find({a:5}).sort({b:1,c:1}) qualifies; omitting equality on a generally prevents that suffix from supplying global order through one scan. (high confidence) [source]
- When an equality predicate fixes the leading field, a single remaining sort field can use either scan direction. For {type:1,timestamp:1}, find({type:"click"}).sort({timestamp:-1}) can use the same index as the ascending timestamp query. (high confidence) [source]
How-to and procedures
- Use explain to distinguish index access from order provision: IXSCAN identifies index access, while an explicit SORT indicates additional sorting. Merely seeing an index scan is insufficient evidence that the index supplies the requested order. (medium confidence) [source]
Problems, failure modes and limitations
- For an unconstrained compound sort, sort fields must occur in index field order. An index {a:1,b:-1} can provide {a:1,b:-1} or {a:-1,b:1}, but cannot provide {a:1,b:1}, {a:-1,b:-1}, or the reordered sort {b:-1,a:1}. (high confidence) [source]
Comparisons and alternatives
- For sort preservation, equality fields generally precede sort fields, and range fields follow them. A highly selective range can instead justify ERS and a subsequent blocking sort, so ESR is a guideline rather than a guarantee of the fastest plan. (medium confidence) [source]
Facts and statements
- A compound index orders entries lexicographically by its declared field sequence. Field order therefore affects which query and sort patterns it can serve efficiently. (high confidence) [source]
- A leading index prefix can supply sort order without equality predicates on those sort fields; for example, {a:1,b:1,c:1,d:1} supports sorting by {a:1,b:1} or {a:-1,b:-1}. (high confidence) [source]
- A range predicate does not universally prevent index-provided sorting. With {a:1,b:1,c:1,d:1}, find({a:{$gt:4}}).sort({a:1,b:1}) preserves index order; a range on an omitted preceding field does not generally provide global order for a later sort field. (high confidence) [source]
- Small $in lists used with sort can be expanded into equality-like scans whose outputs are combined by SORT_MERGE; this does not remove the need for compatible suffix sort fields and directions. Larger lists can behave like ranges, preventing later index fields from providing sort order. (medium confidence) [source]
- SERVER-14070 records a historical planner defect where {a:1,b:1} and find({a:1,b:1}).sort({b:1}) unnecessarily required sorting. The issue lists fixes in 2.6.11 and 2.8.0-rc3; this is historical evidence, not a claim that modern versions retain the bug. (low confidence) [source]
- A range predicate does not universally disqualify index-provided sorting. For {a:1,b:1}, find({a:{$gt:4}}).sort({a:1,b:1}) retains index order; the difficulty is skipping a varying preceding key while sorting a later key alone. (high confidence) [source]
- Inference for SORT_MERGE: equality expansion can expose ordered suffix streams, but it does not remove the suffix field-order and direction requirements. Qualify each branch against those requirements before attributing a missing blocking sort to an ordered merge. (medium confidence) [source]
Children
- Equality-constrained index suffix sorting (frontier)
- Forward and reverse index traversal (frontier)
- ESR versus ERS tradeoffs (frontier)
Frontier under this node: ESR versus ERS tradeoffs, Equality-constrained index suffix sorting, Forward and reverse index traversal