MongoDB Indexing Strategies That Scale: ESR, Partial Indexes and Explain Plans

MongoDB makes it easy to start without thinking about indexes, and that is exactly why so many production clusters end up with forty of them on one collection and still run collection scans. Good index design is not about adding more indexes. It is about matching a small number of compound indexes to the queries your product actually runs.

Start from the query shapes

List the ten queries that matter most, with their filters, sort and expected result size. The query shape — not the collection schema — decides the index. For a multi-tenant orders collection, that list usually looks like "orders for a tenant, filtered by status, newest first".

The ESR rule

For compound indexes, order fields as Equality, then Sort, then Range. Equality fields narrow the index to a contiguous segment, the sort field lets MongoDB return documents in order without an in-memory sort, and range predicates come last.

// Query: tenant + status (equality), newest first (sort), last 30 days (range)
db.orders.find({ tenantId, status: 'paid', createdAt: { $gte: since } }).sort({ createdAt: -1 });

// Index that follows ESR — createdAt serves both the sort and the range
db.orders.createIndex({ tenantId: 1, status: 1, createdAt: -1 });

Read the explain plan, not the timing

Timings vary with cache state. The explain output does not. Run explain('executionStats') and compare three numbers:

• nReturned — documents returned.
• totalKeysExamined — index entries scanned.
• totalDocsExamined — documents fetched.

A healthy query has all three close together. If documents examined is far above documents returned, the index is not selective enough. A SORT stage in the winning plan means the sort is happening in memory and will fail or slow down as data grows.

Partial, unique and TTL indexes

Partial indexes cover only the documents a query needs, which keeps them small and cheap to maintain.

// Only open tickets are queried by SLA deadline — index just those
db.tickets.createIndex({ resolveDue: 1 }, { partialFilterExpression: { status: { $in: ['open', 'assigned', 'in_progress'] } } });

// Unique only among active records (soft-deleted rows may repeat)
db.users.createIndex({ email: 1 }, { unique: true, partialFilterExpression: { deletedAt: null } });

// Expire rate-limit buckets automatically
db.rate_limits.createIndex({ expiresAt: 1 }, { expireAfterSeconds: 0 });

Remember that a query only uses a partial index when its filter is guaranteed to match the partial filter expression.

Retire indexes safely

Every index slows writes and consumes memory. Use $indexStats to find indexes with no recent operations, then hide them before dropping so you can roll back instantly if something regresses.

db.orders.aggregate([{ $indexStats: {} }]);
db.orders.hideIndex('status_1_createdAt_-1'); // planner ignores it; unhide if latency moves

Warning: $indexStats counters reset when a node restarts. Check every replica set member and look at a full business cycle before deciding an index is unused.

Key takeaways

• Design indexes from the top query shapes, not from the schema.
• Order compound index fields Equality → Sort → Range.
• Judge plans by keys and documents examined versus returned.
• Use partial and TTL indexes to keep indexes small and data tidy.
• Hide before you drop.