MongoDB $indexStats Unused & Redundant Index Hunter (mongosh)

Supanote Core Team @supanote · 2 min read

code (javascript) 1 day ago · 58 lines · 2 views

Artifact

Verified Blueprint MongoDB 8.0+

Scan all MongoDB collections via $indexStats to detect zero-read and left-prefix redundant indexes, generating safe hideIndex() commands.

✓ Detects both 0-ops unused indexes and compound left-prefix duplicates wasting WiredTiger RAM
✓ Skips _id, TTL (expireAfterSeconds), and unique constraint indexes automatically for safety
✓ Outputs reversible db.collection.hideIndex() commands before permanent dropIndex() cleanup

Quick Fetch

// MongoDB Unused & Left-Prefix Redundant Index Auditor (mongosh 8.0+)
// Execute: mongosh "$MONGODB_URI" --quiet mongodb-index-hunter.js

const targetDbName = db.getName();
const minAgeDays = 7;
const cutoffMs = Date.now() - minAgeDays * 86400 * 1000;

function keySignature(keyObj) {
  return Object.entries(keyObj).map(([k, v]) => `${k}:${v}`);
}

function isLeftPrefix(shorter, longer) {
  if (shorter.length >= longer.length) return false;
  return shorter.every((entry, idx) => longer[idx] === entry);
}

print(`=== Auditing Indexes in Database: ${targetDbName} (min age: ${minAgeDays}d) ===\n`);

for (const collInfo of db.getCollectionInfos({ type: 'collection' })) {
  const collName = collInfo.name;
  if (collName.startsWith('system.')) continue;

  const coll = db.getCollection(collName);
  const stats = coll.aggregate([{ $indexStats: {} }]).toArray();
  const specs = new Map(coll.getIndexes().map((ix) => [ix.name, ix]));

  const parsed = stats.map((st) => ({
    name: st.name,
    ops: Number(st.accesses?.ops || 0),
    since: new Date(st.accesses?.since || Date.now()),
    sig: keySignature(st.key),
    spec: specs.get(st.name) || {},
  }));

  for (const idx of parsed) {
    if (idx.name === '_id_') continue;
    // Never auto-hide unique constraints or TTL indexes!
    if (idx.spec.unique || typeof idx.spec.expireAfterSeconds === 'number' || idx.spec.hidden) {
      continue;
    }

    const coveringIndex = parsed.find(
      (other) => other.name !== idx.name && !other.spec.sparse && !other.spec.partialFilterExpression && isLeftPrefix(idx.sig, other.sig)
    );

    if (coveringIndex) {
      print(`[REDUNDANT PREFIX] ${collName}.${idx.name} covered by ${coveringIndex.name}`);
      print(`  -> db.getCollection("${collName}").hideIndex("${idx.name}");\n`);
      continue;
    }

    if (idx.ops === 0 && idx.since.getTime() <= cutoffMs) {
      print(`[UNUSED 0 OPS] ${collName}.${idx.name} (0 reads since ${idx.since.toISOString()})`);
      print(`  -> db.getCollection("${collName}").hideIndex("${idx.name}");\n`);
    }
  }
}

1// MongoDB Unused & Left-Prefix Redundant Index Auditor (mongosh 8.0+)

2// Execute: mongosh "$MONGODB_URI" --quiet mongodb-index-hunter.js

4const targetDbName = db.getName();

5const minAgeDays = 7;

6const cutoffMs = Date.now() - minAgeDays * 86400 * 1000;

8function keySignature(keyObj) {

9 return Object.entries(keyObj).map(([k, v]) => `${k}:${v}`);

10}

12function isLeftPrefix(shorter, longer) {

13 if (shorter.length >= longer.length) return false;

14 return shorter.every((entry, idx) => longer[idx] === entry);

15}

17print(`=== Auditing Indexes in Database: ${targetDbName} (min age: ${minAgeDays}d) ===\n`);

19for (const collInfo of db.getCollectionInfos({ type: 'collection' })) {

20 const collName = collInfo.name;

21 if (collName.startsWith('system.')) continue;

23 const coll = db.getCollection(collName);

24 const stats = coll.aggregate([{ $indexStats: {} }]).toArray();

25 const specs = new Map(coll.getIndexes().map((ix) => [ix.name, ix]));

27 const parsed = stats.map((st) => ({

28 name: st.name,

29 ops: Number(st.accesses?.ops || 0),

30 since: new Date(st.accesses?.since || Date.now()),

31 sig: keySignature(st.key),

32 spec: specs.get(st.name) || {},

33 }));

35 for (const idx of parsed) {

36 if (idx.name === '_id_') continue;

37 // Never auto-hide unique constraints or TTL indexes!

38 if (idx.spec.unique || typeof idx.spec.expireAfterSeconds === 'number' || idx.spec.hidden) {

39 continue;

40 }

42 const coveringIndex = parsed.find(

43 (other) => other.name !== idx.name && !other.spec.sparse && !other.spec.partialFilterExpression && isLeftPrefix(idx.sig, other.sig)

44 );

46 if (coveringIndex) {

47 print(`[REDUNDANT PREFIX] ${collName}.${idx.name} covered by ${coveringIndex.name}`);

48 print(` -> db.getCollection("${collName}").hideIndex("${idx.name}");\n`);

49 continue;

50 }

52 if (idx.ops === 0 && idx.since.getTime() <= cutoffMs) {

53 print(`[UNUSED 0 OPS] ${collName}.${idx.name} (0 reads since ${idx.since.toISOString()})`);

54 print(` -> db.getCollection("${collName}").hideIndex("${idx.name}");\n`);

55 }

56 }

57}

Replies 0

Reply with note

No replies yet

Every reply is a note. Start a discussion, ask a question, or attach a code snippet.

Reply with a note

Related MongoDB 8.0+ & Database, Cache & SQL Blueprints

Production-ready configurations and diagnostics from the verified Supanote library.

All Database, Cache & SQL →

YAML MongoDB 8.0+

MongoDB 8.0 Production mongod.conf (WiredTiger & Zstd)

Production MongoDB 8.0 mongod.conf tuned for WiredTiger cache sizing, Zstandard block/journal compression, and workingMillis profiling.

JAVASCRIPT MongoDB 8.0+

MongoDB 8.0+ setQuerySettings: Pin Index Hints Without Redeploy

Enforce persistent MongoDB 8.0+ indexHints, maxTimeMS, or reject runaway COLLSCAN query shapes cluster-wide via mongosh without redeploying.

JAVASCRIPT Memcached 1.6.34+

Memcached Meta Protocol (mg/ms) Anti-Stampede & Stale-While-Revalidate

Zero-dependency Node.js client for Memcached 1.6+ Meta Protocol (mg N/W/Z/X flags) preventing cache stampedes with stale-while-revalidate.

INI Redis 8.0+

Redis 8.0 Async io-threads & Linux Kernel Tuning (redis.conf)

Unlock up to +112% throughput in Redis 8.0 with the redesigned async io-threads engine, lazyfree non-blocking eviction, and active defrag.

Interactive Developer Workbench: Online JSON Formatter, Validator & Viewer

Validate, pretty-print, minify, and inspect JSON API responses with exact syntax error pinpointing.

Open JSON Formatter →