Redundant (prefix) index detection for compound indexes
Parent: Hidden indexes and safe index removal · Published reference · snapshot 2026-10-30
Also known as: Redundant and prefix index detection
↓ Facts as markdownall context files
Source-anchored research for Redundant (prefix) index detection for compound indexes. 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
- MongoDB explicitly permits removing a prefix index when neither it nor the covering compound index has a sparse or unique constraint; this rule requires the other index semantics to support the same queries. (high confidence) [source]
- MongoDB master planner code in cmpIndexAssignments declines interchangeability for non-B-tree, unique, sparse, collated, or partial indexes, and when both indexes are multikey. This is query-plan pruning logic, not a rule authorizing physical index removal. (low confidence) (verified as of 2026-10-30) [source]
- A trailing-field index is not redundant merely because its field occurs in a compound index: {b:-1} is not a prefix of {a:1,b:-1}. (high confidence) [source]
- Do not remove a unique prefix index solely because a longer compound index exists: a unique compound index enforces uniqueness of the complete key combination, so uniqueness of {a:1,b:1} does not enforce uniqueness of a alone. (high confidence) [source]
- A partial compound index is not an unconditional replacement for a full prefix index. MongoDB rejects partial-index use when it would omit matching documents; the query must imply the partial filter. (low confidence) [source]
How-to and procedures
- Check application hint() calls before removing a prefix index; Percona specifically warns that application hints may depend on the index selected for removal. (low confidence) [source]
Comparisons and alternatives
- A practical audit lists index definitions, compares ordered key patterns for prefix relationships, and checks representative query execution plans. These checks identify consolidation candidates rather than proving every shorter index can be removed. (high confidence) [source]
- A practical detector inventories getIndexes()/listIndexes output and compares ordered key patterns for exact matches or leading-prefix relationships. Treat these matches as candidates requiring semantic review, rather than automatic deletion instructions. (medium confidence) [source]
Facts and statements
- Index prefixes are contiguous leading subsets: {a:1,b:1,c:1} has prefixes {a:1} and {a:1,b:1}; {b:1} and {a:1,c:1} are not prefixes. (high confidence) [source]
- Atlas Performance Advisor flags matching-prefix indexes in the same collection as redundant and displays related covering indexes for manual verification. With {a:1}, {b:-1}, and {a:1,b:-1}, it identifies {a:1}, not {b:-1}, as redundant. (low confidence) (verified as of 2026-10-30) [source]
- Percona explicitly warns to check application hint() use before dropping a prefix index. Its sample prefix detector compares serialized key patterns, so the script does not itself validate unique, sparse, collation, or partial-filter semantics. (medium confidence) [source]
- MongoDB master planner code prefers a smaller key pattern for interchangeable assignments after guarding sort and projection coverage; its comments note the longer index may carry more data and entries. Prefix query support therefore does not prove identical execution cost. (low confidence) (verified as of 2026-10-30) [source]
- Unnecessary indexes consume storage and impose maintenance work on writes that affect their entries; reducing genuine redundancy can reduce that overhead, without implying a universal throughput multiplier. (high confidence) [source]
- A compound index {a:1,b:1,c:1} supports its leading prefixes {a:1} and {a:1,b:1}; these shorter indexes are prefix-redundancy candidates. (high confidence) [source]
- OneUptime recommends explain("executionStats") to inspect which index serves a representative prefix query. This is a useful workload check, but one winning plan does not establish equivalence for every query shape. (low confidence) [source]
Children
- No children recorded.