# Unique, TTL and shard-key indexes that read as unused > Source-anchored research for Unique, TTL and shard-key indexes that read as unused. Run status: COMPLETED. Parent: [Unused index detection with $indexStats](https://llms-explorer.com/tree/unused-index-detection-with-indexstats/) · 4 facets · 14 facts · page: https://llms-explorer.com/tree/unique-ttl-and-shard-key-indexes-that-read-as-unused/ ## How it works - A unique index can enforce uniqueness while accesses.ops remains zero: checking uniqueness during INSERT does not increment this counter. Removing an index solely because of zero accesses can remove a required integrity constraint. (high confidence) — [source](https://www.percona.com/blog/identifying-unused-indexes-in-mongodb/) ## Problems, failure modes and limitations - $indexStats accesses.ops measures index access driven by user requests; it excludes internal TTL deletion and chunk split or migration activity. A zero counter therefore cannot establish that an index has no operational purpose. (high confidence) — [source](https://www.mongodb.com/docs/manual/reference/operator/aggregation/indexStats.md) - Hiding preserves uniqueness enforcement, TTL expiration, and index maintenance. Inference: a successful hidden-index trial tests query dependence but cannot establish that removing a unique or TTL index preserves application semantics. (high confidence) — [source](https://www.mongodb.com/docs/manual/core/index-hidden.md) - For ordinary shard-key index protection, the supporting index may be the shard-key index itself or a compound index with the shard key as its prefix. MongoDB documents that the only non-hidden supporting index cannot be hidden or dropped; the hashed-key exception requires separate handling. (high confidence) (verified as of 2026-10-30) — [source](https://www.mongodb.com/docs/manual/core/sharding-shard-key-indexes/) - The blanket assertion that every shard-key index must always remain is too broad. MongoDB introduced dropping hashed shard-key indexes in 7.0.3, 6.0.12 and 5.0.22; its documented procedure warns that balancing for the collection stops and orphan cleanup cannot proceed without that index. It recommends a supporting non-hashed index and checking for orphans before removal. (high confidence) — [source](https://www.mongodb.com/docs/manual/tutorial/drop-a-hashed-shard-key-index/) - An audit must preserve index properties alongside usage counts. The My DBA World script projects only name, since, and accesses, while $indexStats supplies a full spec. Inference: that script output alone cannot identify unique or TTL constraints. (medium confidence) — [source](https://www.mydbaworld.com/indexes-usage-all-collections-all-databases-mongodb/) ## Comparisons and alternatives - On ranged sharded collections, unique indexes generally require the shard key as a prefix, with the default _id index as an exception. If _id is not the shard key, its index enforces uniqueness per shard rather than across the cluster; index review must preserve the actual constraint scope. (high confidence) — [source](https://www.mongodb.com/docs/manual/core/index-unique.md) - An automated audit should inspect the index specification and shard-key support before acting on zero accesses. The full spec is included in $indexStats; identify TTL indexes by the presence of expireAfterSeconds, rather than a truthiness check that would miss expireAfterSeconds: 0. Excluding unique and required shard-key indexes is a conservative policy supported by the tool examples. (high confidence) — [source](https://www.mongodb.com/docs/manual/reference/operator/aggregation/indexStats.md) ## Facts and statements - A TTL index may expire documents while its recorded accesses remain zero. Treat expireAfterSeconds as evidence of a retention function that must be reviewed separately from query usage. (high confidence) — [source](https://www.mongodb.com/docs/manual/reference/operator/aggregation/indexStats.md) - A historical MongoDB production incident on 4.4.18 involved dropping a shard-key index on one shard, followed by queued deletion tasks and degraded performance. The ticket reports rebuilding the index and scaling up to let deletions catch up; it does not establish that an unused-index tool caused the drop. (low confidence) — [source](https://jira.mongodb.org/browse/SERVER-76973) - The mdbkit author excludes unique, TTL and required shard-key indexes from unused-index candidates. Indexterity describes removing zero-usage advisories for these protected types after a test where a unique index rejected a duplicate while remaining at ops zero. These are independent tool-author implementations of conservative classification, not independent proofs of MongoDB semantics. (medium confidence) (verified as of 2026-10-24) — [source](https://github.com/saqibameen86/mdbkit) - The blanket rule that shard-key indexes can never be dropped is too broad: MongoDB permits dropping hashed shard-key indexes starting in 7.0.3, 6.0.12, and 5.0.22. This exception requires a separate operational review. (high confidence) (verified as of 2026-10-30) — [source](https://www.mongodb.com/docs/manual/core/sharding-shard-key-indexes/) - The hashed-index drop procedure warns that removal disables balancing for that collection and prevents remaining orphan cleanup. It recommends a supporting non-hashed index to avoid shard-key-filter collection scans; recreate the hashed index to restore balancing eligibility. (low confidence) (verified as of 2026-10-30) — [source](https://www.mongodb.com/docs/manual/tutorial/drop-a-hashed-shard-key-index/) - Treat zero-access indexes as review candidates: inspect unique and TTL properties in spec, identify shard-key support from collection metadata, and approve any semantic or sharding change explicitly before removal. This is a synthesis of the documented exceptions. (medium confidence) — [source](https://www.mongodb.com/docs/manual/reference/operator/aggregation/indexStats.md)