Index Analysis and Optimization

Intermediate15 minTrending

Analyze query performance with explain plans, identify missing indexes, and optimize existing indexes to speed up MongoDB queries.

Prerequisites

-MongoDB 6+ with mongosh
-A database with collections and data to analyze

Steps

Analyze a query with explain

Run explain on a query to see if it uses an index or performs a full collection scan.

$ mongosh --eval 'db.orders.find({customerId: "c1001", status: "pending"}).explain("executionStats")' mydb

Look for COLLSCAN in the winning plan. That means no index is being used and the query scans every document.

List all indexes on a collection

View existing indexes and their key patterns to understand current coverage.

$ mongosh --eval 'db.orders.getIndexes()' mydb

Create a compound index for a common query

Add an index that covers frequently queried field combinations.

$ mongosh --eval 'db.orders.createIndex({customerId: 1, status: 1, createdAt: -1}, {name: "idx_customer_status_date"})' mydb

Order index fields by equality filters first, then sort fields, then range filters (ESR rule).

Find unused indexes

Identify indexes that consume storage but are never used by any query.

$ mongosh --eval 'db.orders.aggregate([{$indexStats: {}}, {$project: {name: 1, accesses: "$accesses.ops", since: "$accesses.since"}}])' mydb

Index stats reset on server restart. Check over a full business cycle (at least a week) before dropping unused indexes.

Check index sizes

View how much memory each index consumes to prioritize optimization.

$ mongosh --eval 'db.orders.stats().indexSizes' mydb

Full Script

full-script.sh

#!/bin/bash
# MongoDB Index Optimization Report
set -euo pipefail

DB="mydb"

echo "=== Collection stats ==="
mongosh --quiet --eval "
const collections = db.getCollectionNames();
collections.forEach(c => {
  const stats = db[c].stats();
  print(c + ': ' + stats.count + ' docs, ' +
    Math.round(stats.size / 1024 / 1024) + 'MB data, ' +
    Math.round(stats.totalIndexSize / 1024 / 1024) + 'MB indexes, ' +
    stats.nindexes + ' indexes');
});
" "$DB"

echo ""
echo "=== Index usage stats (all collections) ==="
mongosh --quiet --eval "
const collections = db.getCollectionNames();
collections.forEach(c => {
  print('--- ' + c + ' ---');
  const stats = db[c].aggregate([{$indexStats: {}}]).toArray();
  stats.forEach(s => {
    print('  ' + s.name + ': ' + s.accesses.ops + ' ops since ' + s.accesses.since);
  });
});
" "$DB"

echo ""
echo "=== Potentially unused indexes (0 ops) ==="
mongosh --quiet --eval "
const collections = db.getCollectionNames();
collections.forEach(c => {
  const stats = db[c].aggregate([{$indexStats: {}}]).toArray();
  stats.filter(s => s.accesses.ops === 0 && s.name !== '_id_').forEach(s => {
    print(c + '.' + s.name + ' (0 ops since ' + s.accesses.since + ')');
  });
});
" "$DB"

echo ""
echo "=== Index sizes ==="
mongosh --quiet --eval "
const collections = db.getCollectionNames();
collections.forEach(c => {
  const sizes = db[c].stats().indexSizes;
  Object.keys(sizes).forEach(idx => {
    print(c + '.' + idx + ': ' + Math.round(sizes[idx] / 1024) + 'KB');
  });
});
" "$DB"

FAQ

Discussion

Loading comments...

#!/bin/bash # MongoDB Index Optimization Report set -euo pipefail DB="mydb" echo "=== Collection stats ===" mongosh --quiet --eval " const collections = db.getCollectionNames(); collections.forEach(c => { const stats = db[c].stats(); print(c + ': ' + stats.count + ' docs, ' + Math.round(stats.size / 1024 / 1024) + 'MB data, ' + Math.round(stats.totalIndexSize / 1024 / 1024) + 'MB indexes, ' + stats.nindexes + ' indexes'); }); " "$DB" echo "" echo "=== Index usage stats (all collections) ===" mongosh --quiet --eval " const collections = db.getCollectionNames(); collections.forEach(c => { print('--- ' + c + ' ---'); const stats = db[c].aggregate([{$indexStats: {}}]).toArray(); stats.forEach(s => { print(' ' + s.name + ': ' + s.accesses.ops + ' ops since ' + s.accesses.since); }); }); " "$DB" echo "" echo "=== Potentially unused indexes (0 ops) ===" mongosh --quiet --eval " const collections = db.getCollectionNames(); collections.forEach(c => { const stats = db[c].aggregate([{$indexStats: {}}]).toArray(); stats.filter(s => s.accesses.ops === 0 && s.name !== '_id_').forEach(s => { print(c + '.' + s.name + ' (0 ops since ' + s.accesses.since + ')'); }); }); " "$DB" echo "" echo "=== Index sizes ===" mongosh --quiet --eval " const collections = db.getCollectionNames(); collections.forEach(c => { const sizes = db[c].stats().indexSizes; Object.keys(sizes).forEach(idx => { print(c + '.' + idx + ': ' + Math.round(sizes[idx] / 1024) + 'KB'); }); }); " "$DB"