Index Performance

Index Performance

Creating indexes is only half the battle. You also need to know whether MongoDB is actually using them. That's where explain() comes in. It's like having X-ray vision for your queries — you can see exactly how MongoDB executes them and whether indexes are being leveraged.

The explain() method is your best friend for debugging slow queries. It tells you which indexes were considered, which one was chosen, and how many documents MongoDB had to examine. If you see "totalDocsExamined" in the millions, something's wrong.

The explain() Method

Using explain is simple — just chain it to the end of any query. You can pass "executionStats" for detailed performance metrics or "allPlansExecution" to see all alternative plans MongoDB considered.

db.users.find({ email: "test@example.com" }).explain("executionStats")

Look at the winningPlan section. If you see "stage": "IXSCAN", MongoDB used an index. If you see "stage": "COLLSCAN", it did a full collection scan — meaning no index was used for that query. That's your signal to create or fix an index.

Pay attention to totalKeysExamined versus totalDocsExamined. In an ideal scenario, these numbers are close. If keys examined is much higher than docs examined, your index might be too broad. If docs examined is much higher than keys, your index isn't selective enough.

Understanding Query Plans

MongoDB's query planner picks the best index for each query. When you run explain, you see the winning plan and any rejected alternatives. This is useful because sometimes MongoDB picks an index you didn't expect.

The query planner caches plans for a while. If your data distribution changes significantly, the cached plan might become suboptimal. You can force a re-evaluation by adding { planCacheKey: true } to your explain, but that's an advanced move.

db.orders.find({ userId: ObjectId("..."), status: "shipped" }).explain("allPlansExecution")

The rejected plans show you what else MongoDB considered. If a rejected plan looks better than the winner, you might need to adjust your index. Maybe add a field or change the order to make the better plan win.

Index Usage Statistics

MongoDB tracks how often each index is used. Run this aggregation to see your index usage stats across all collections:

db.getCollectionNames().forEach(function(coll) {
  var stats = db[coll].aggregate([{ $indexStats: {} }]).toArray();
  print(coll + ":");
  stats.forEach(function(s) {
    print("  " + s.name + " - accesses: " + JSON.stringify(s.accesses));
  });
});

Look for indexes with zero or very low access counts. These are dead weight — they're slowing down your writes but nobody's actually using them for queries. It's like maintaining a bike you never ride.

You can also use db.stats() and db.collection.stats() to see total index sizes. Large unused indexes waste both disk space and memory, since MongoDB tries to keep index pages in RAM.

Removing Unused Indexes

Once you identify unused indexes, drop them without mercy. Every unnecessary index adds write overhead and consumes resources. This is especially important in production where write performance matters.

db.users.dropIndex("rarely_used_field_1")

Before dropping an index, double-check it's not used by any scheduled jobs, aggregation pipelines, or reporting queries that run monthly or quarterly. You don't want to drop an index just before month-end reports need it.

A healthy database has just enough indexes to support its query patterns without excess. Think of it as a well-organized toolbox — every tool has a purpose, and you don't carry around tools you never use.