MongoDB Indexes: What to Create and How to Verify It Worked

An unindexed MongoDB query does a collection scan. On 10 million documents that is seconds per query, and the fix is almost always one createIndex call — provided the fields are in the right order.

The ESR rule for compound indexes#

Order the fields Equality, Sort, Range:

// Query: equality on status, sort by createdAt, range on score
db.orders.find({ status: 'active', score: { $gt: 50 } }).sort({ createdAt: -1 });
 
// Correct index
db.orders.createIndex({ status: 1, createdAt: -1, score: 1 });

Why that order: equality fields narrow the index to a contiguous section; within that section the sort field is already ordered, so no in-memory sort is needed; the range field is scanned last. Putting the range before the sort forces a sort, which is the most common compound-index mistake.

Prefixes matter#

An index on { a: 1, b: 1, c: 1 } serves queries on:

• { a }
• { a, b }
• { a, b, c }

It does not serve { b } or { b, c } — the leftmost field must be present. This is why three separate single-field indexes are not equivalent to one compound index, and also why you often need fewer indexes than you think.

Covered queries never touch a document#

If every field in the query and the projection is in the index, MongoDB answers from the index alone:

db.users.createIndex({ email: 1, name: 1 });
 
// Covered: _id excluded, and both fields are in the index
db.users.find({ email: 'a@b.com' }, { _id: 0, name: 1 });

explain() shows totalDocsExamined: 0 when this happens. Note the _id: 0 — _id is returned by default and is not in the index, so leaving it in prevents coverage.

Verify with explain()#

db.orders.find({ status: 'active' }).explain('executionStats');

Read these four fields:

The ratio that matters is docs examined to docs returned. Examining 100,000 documents to return 10 means the index is not selective enough, even though it is being used. A ratio near 1:1 is the goal.

If you see SORT as a stage, the sort happened in memory — and MongoDB aborts an in-memory sort above 100 MB, so this is a latent failure as well as a slow query.

Index types beyond the default#

// Multikey — automatic on an array field. One index entry per element.
db.posts.createIndex({ tags: 1 });
 
// Text search, with weights so a title match outranks a body match
db.posts.createIndex(
  { title: 'text', body: 'text' },
  { weights: { title: 10, body: 1 } },
);
 
// Partial — index only the documents you query
db.orders.createIndex(
  { createdAt: -1 },
  { partialFilterExpression: { status: 'active' } },
);
 
// TTL — documents expire automatically, ideal for sessions and logs
db.sessions.createIndex({ createdAt: 1 }, { expireAfterSeconds: 3600 });
 
// Unique — a constraint, enforced by the index
db.users.createIndex({ email: 1 }, { unique: true });

Two constraints worth knowing: you can have only one text index per collection (it may cover several fields), and you cannot create a compound index on two array fields.

The cost side#

Every index is written on every insert and update, and consumes RAM in the working set. Two habits keep this honest:

// Which indexes are actually used?
db.orders.aggregate([{ $indexStats: {} }]);

Drop indexes with zero accesses.ops after a representative period. And build indexes on a live system with { background: true } on older versions — modern MongoDB does this by default, but a foreground build locks the database.

Frequently asked questions#

Does index order matter for equality-only queries?#

For which queries the index can serve, yes — the prefix rule applies regardless. For selectivity within a set of equality fields, put the most selective first.

Should I index every field I filter on?#

No. Index the queries you actually run, verified by $indexStats and slow-query logs. Speculative indexes cost writes for nothing.

Why is my unique index failing to build?#

Existing duplicates. Find them with an aggregation grouping by the field and filtering count > 1 before creating the index.

Do indexes help writes at all?#

Only indirectly: an update with a filter needs to find the document, and an index makes that fast. The write itself is always slower with more indexes.

Related reading#

• MongoDB Schema Design
• SQL Query Optimization

References#

• MongoDB docs: Indexes
• MongoDB docs: ESR Rule