Leading-prefix redundant index detection
Parent: Unused index detection with $indexStats · Published reference · snapshot 2026-10-31
↓ Facts as markdownall context files
Source-anchored research for Leading-prefix redundant index detection. 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
- A leading prefix is a contiguous beginning subset of an ordered index key pattern. For {a:1,b:-1}, {a:1} is a prefix candidate; {b:-1} is not. Matching an unordered subset of fields is insufficient. (high confidence) [source]
- A partial compound index is not automatically a replacement for a full prefix index. MongoDB rejects partial-index plans that would omit matching documents; independent detector policies therefore check filter compatibility or conservatively exclude partial indexes. (high confidence) [source]
How it works
- MongoDB documents that, when neither index has a sparse or unique constraint, a compound index can replace its prefix index for query use. Independent detector documentation adds option-compatibility checks, so this rule should not be generalized to every index sharing leading field names. (high confidence) [source]
- Independent detector policies protect TTL and unique indexes because their expiration or constraint roles are not captured by query-use counts; mdbkit also excludes hidden indexes and the index needed by the shard key. These are tool policies, not a claim that every such index is universally undroppable. (medium confidence) [source]
How-to and procedures
- Check application hints before retiring a prefix index. Percona warns about hint() dependencies, and Indexterity describes a hinted index as a veto because removing the referenced name or key pattern can turn queries into errors. (high confidence) [source]
Problems, failure modes and limitations
- A detector must preserve a unique prefix index when the proposed survivor does not preserve that constraint: unique {a:1} cannot be replaced by non-unique {a:1,b:1}. (medium confidence) [source]
Facts and statements
- Structural redundancy and observed non-use are different checks. Percona documents prefix comparisons under check-duplicated, while check-unused reports $indexStats entries with accesses.ops=0; Atlas likewise distinguishes prefix redundancy from its unused-index recommendations. (high confidence) [source]
- Percona’s published worked example successfully creates {name:1} after {name:1,age:1} exists. This historical example shows that prefix redundancy detection must not be assumed to be an automatic createIndex rejection rule. (low confidence) [source]
- For ordinary indexes, hiding a suspected redundant index permits evaluation before dropping it, and unhiding avoids the cost of rebuilding a removed index. This is a validation step after structural detection. (low confidence) [source]
- Indexterity reports a false-positive path: its collector converted an unrecognized legacy 2d key to 1, making it resemble an ordinary ascending prefix and qualify as DROP_REDUNDANT. Preserve index key types during normalization; this implementation-specific report has not been independently reproduced here. (low confidence) [source]
- SERVER-27464 documents a distinct duplicate-detection normalization issue: differently spelled numeric directions, such as {a:1} and {a:2}, produced separate indexes in the issue’s reproduction. Treat this as historical evidence about normalization, not proof of current-version behavior. (low confidence) [source]
- Collation and index options belong in the redundancy decision, not just key names. Indexterity documents compatibility checks for collation, sparse behavior, partial filters, TTL, and index type; mdbkit requires matching collation and excludes partial, sparse, and special indexes from coverage comparisons. (medium confidence) [source]
- Percona documents pt-mongodb-index-check check-duplicated, check-unused, and check-all, plus --json output. Its duplication example compares indexes with the same ordered leading keys; this documentation does not establish that the checker implements all option-compatibility safeguards. (low confidence) (verified as of 2026-10-30) [source]
- Atlas documents a Redundant badge for an index matching another index prefix and displays related covering indexes for safety review. Those related indexes are survivors to examine, not removal recommendations. (low confidence) (verified as of 2026-10-14) [source]
- Hiding a candidate index can evaluate the effect of removal on query planning before dropping it. A hidden index remains maintained and consumes storage, so this experiment does not itself realize the write and storage savings of dropping it. (medium confidence) [source]
Children
- No children recorded.