MongoDB index optimization: keep the wins, prune the waste

MongoDB index optimization is a balance, not a pile. Every index speeds up a read and taxes every write, and each one takes storage and memory. The goal is the smallest set of indexes that covers your real queries. Anything past that is pure cost, paid on every insert and update, quietly, forever.

Here is how to see which indexes earn their keep, choose the right type, and prune the rest.

Find the indexes nobody uses

MongoDB tracks how often each index is actually used. Ask it:

db.orders.aggregate([{ $indexStats: {} }])

Each index reports its access count since the server last started:

{ name: "userId_1_status_1", accesses: { ops: 48213, since: ... } }
{ name: "legacy_email_1",    accesses: { ops: 0,     since: ... } }   // retirement candidate

ops: 0 applies only to the node where $indexStats runs. The observation begins at since, and restart or index modification resets it. Before a permanent change, check every relevant member and cover periodic workloads that may not run daily. The $indexStats reference defines those limits.

db.orders.hideIndex("legacy_email_1")
// observe a representative workload; restore immediately if plans regress
db.orders.unhideIndex("legacy_email_1")

// after the hidden trial remains healthy
db.orders.dropIndex("legacy_email_1")

Hidden indexes stay maintained, continue enforcing unique and TTL behavior, and still consume storage and write cost. That is useful during a trial because restoration needs no rebuild. The optimization loop is: measure the right window, hide, observe real plans and latency, restore on regression, and drop only after review.

Pick the index type that fits the query

MongoDB has more than one kind of index, and the right one depends on the query, not on habit. The common types:

• Single field: { createdAt: -1 }. The starting point for one filter or one sort.
• Compound: { userId: 1, status: 1, createdAt: -1 }. Covers a multi-field filter plus a sort. Order by Equality, Sort, Range.
• TTL: { createdAt: 1 } with expireAfterSeconds. Expires old documents on a schedule.
• Unique: { email: 1 } with unique: true. Enforces no two documents share a value.
• Sparse or partial: index only the documents that have the field, or match a filter. Smaller index, less write cost.
• Text and geospatial: full-text search and location queries, when you need them.

One well-ordered compound index frequently replaces three single-field ones, which is a net win: fewer indexes to maintain, less write amplification, same query coverage. The MongoDB indexes manual covers each type in depth.

The TTL index that never fires

A common miss: a TTL index pointed at the wrong field, or set on a collection whose documents were backfilled with an old date. It looks like it should be reclaiming space, and it reclaims nothing.

db.sessions.createIndex({ createdAt: 1 }, { expireAfterSeconds: 3600 })

MongoDB runs the TTL sweep about once a minute, deleting documents whose createdAt is older than an hour. If createdAt is missing or is a string instead of a Date, the document never expires. Worth checking the field type if your "self-cleaning" collection is not shrinking. That check is a schema question, covered in MongoDB schema analysis.

Or see the whole index budget at once

mongoui's Index and Storage insight shows node-local usage with its observation start, conservative plain B-tree prefix overlap, hidden state, and storage. It never treats unique, sparse, partial, TTL, collated, wildcard, special-key, or hidden indexes as interchangeable prefix replacements. Reviewed Hide and Restore actions keep a recovery path open, while permanent Drop remains an explicit typed-name decision.

Index inventory runs on your machine and makes no AI call. Full documents and MongoDB credentials stay local. If you explicitly enable AI narration for a query plan, the authored query and bounded structural context go directly to your selected provider. Add the index a slow query needs, drop the three it does not, and keep the write path lean.