Published / updated29 Aug 2026

Indexes and Performance

Before you start

You need: data modelling (Article 05).

Time: about 50 minutes, plus the practice.

Learning objective

Add indexes that measurably change a query plan, and diagnose a slow query from its explain output rather than by guessing.

Topics

Why index
Creating indexes
Compound indexes and field order
Covered queries
Index types
Reading an explain plan
Index costs
Finding slow queries
Aggregation basics

Why index

Without an index, MongoDB reads every document to answer a query — a collection scan.

db.students.find({ rollNumber: "NCA-2024-0012" })

On 400 documents that is instant. On 400,000 it reads all of them, and the query gets slower every month as data grows.

An index is a sorted structure mapping values to documents, so MongoDB jumps directly to the matches. The same trade-off as SQL Server: faster reads, slower writes, more storage.

Every collection has an index on _id, created automatically and undroppable.

Creating indexes

db.students.createIndex({ rollNumber: 1 })                 // ascending
db.students.createIndex({ createdAt: -1 })                 // descending
db.students.createIndex({ schoolId: 1, className: 1, section: 1 })
db.students.createIndex({ schoolId: 1, rollNumber: 1 }, { unique: true })

db.students.getIndexes()
db.students.dropIndex("rollNumber_1")
db.students.totalIndexSize()

Direction is irrelevant for a single-field index — MongoDB can walk it either way. It matters for compound indexes used in sorts.

db.students.createIndex({ schoolId: 1, className: 1 }, { name: "idx_school_class" })

Naming an index makes it identifiable in explain output and droppable by a stable name.

Building an index on a large collection blocks writes on older versions; modern MongoDB builds in the background by default. On a production collection, still schedule it.

Compound indexes and field order

db.students.createIndex({ schoolId: 1, className: 1, section: 1 })

Field order decides which queries the index serves. This index supports:

db.students.find({ schoolId: 1 })
db.students.find({ schoolId: 1, className: "10th" })
db.students.find({ schoolId: 1, className: "10th", section: "A" })

and does not support:

db.students.find({ className: "10th" })              // schoolId missing
db.students.find({ section: "A" })
db.students.find({ className: "10th", section: "A" })

An index can only be used from the left. Think of a phone book sorted by surname then first name: it cannot find everyone called "Ravi".

This is the prefix rule, and it is the single most important thing about compound indexes.

The ESR rule

Order fields as Equality, Sort, Range.

db.students.find({ schoolId: 1, className: "10th", marks: { $gt: 80 } })
           .sort({ name: 1 })

db.students.createIndex({
    schoolId: 1,      // Equality
    className: 1,     // Equality
    name: 1,          // Sort
    marks: 1          // Range
})

Equality fields narrow to a contiguous section of the index. The sort field then reads in order with no in-memory sort. The range field filters last.

Putting the range before the sort forces an in-memory sort, because the matching entries are no longer contiguous in sort order.

In a multi-tenant system, schoolId is always the leading field, because every query filters on it.

Fewer, broader indexes

// Redundant — the first is a prefix of the second
db.students.createIndex({ schoolId: 1 })
db.students.createIndex({ schoolId: 1, className: 1 })

The second serves every query the first does. Drop the first.

One well-ordered compound index usually replaces three single-field ones. MongoDB can intersect indexes but rarely chooses to, so do not rely on it.

Covered queries

db.students.createIndex({ schoolId: 1, className: 1, name: 1, rollNumber: 1 })

db.students.find(
    { schoolId: 1, className: "10th" },
    { name: 1, rollNumber: 1, _id: 0 }
)

Every field needed is in the index, so MongoDB never reads a document. explain shows totalDocsExamined: 0 — the fastest possible query.

Two requirements: every queried and projected field must be in the index, and _id must be excluded unless it is in the index.

Covered queries suit list pages, which need four or five fields from documents holding forty.

Index types

// Multikey — automatic on any array field
db.students.createIndex({ subjects: 1 })
db.students.find({ subjects: "Mathematics" })

MongoDB indexes each element separately. A compound index may contain only one array field — two would produce a combinatorial explosion, and MongoDB refuses.

// Text — word search
db.students.createIndex({ name: "text", rollNumber: "text" })
db.students.find({ $text: { $search: "Ravi Kumar" } })

One text index per collection, covering as many fields as needed. It tokenises words, so it does not do partial-word matching — right for a search box, wrong for autocomplete.

// Partial — index only some documents
db.students.createIndex(
    { schoolId: 1, className: 1 },
    { partialFilterExpression: { status: "Active" } }
)

Smaller and faster when most queries want active students. The query must include the same predicate for the index to be used — { schoolId: 1, className: "10th" } alone will not use it.

// Sparse — skip documents missing the field
db.students.createIndex({ email: 1 }, { sparse: true, unique: true })

A plain unique index treats missing fields as equal, so only one document may omit email. sparse or a partial filter fixes that.

// TTL — delete documents automatically
db.sessions.createIndex({ createdAt: 1 }, { expireAfterSeconds: 3600 })

Right for sessions, tokens and temporary data. The background remover runs about every 60 seconds, so deletion is not immediate.

// Case-insensitive, via a collation
db.students.createIndex({ name: 1 }, { collation: { locale: "en", strength: 2 } })
db.students.find({ name: "ravi kumar" }).collation({ locale: "en", strength: 2 })

The query must specify the same collation or the index is not used — a common reason a case-insensitive index appears to do nothing.

Reading an explain plan

db.students.find({ schoolId: 1, className: "10th" }).explain("executionStats")

{
    executionStats: {
        nReturned: 42,
        executionTimeMillis: 2,
        totalKeysExamined: 42,
        totalDocsExamined: 42,
        executionStages: { stage: "FETCH", inputStage: { stage: "IXSCAN" } }
    }
}

Field	Read it as
`stage: COLLSCAN`	No index used
`stage: IXSCAN`	Index used
`stage: FETCH`	Documents read after the index
`stage: SORT`	In-memory sort — needs an index
`nReturned`	Documents returned
`totalKeysExamined`	Index entries read
`totalDocsExamined`	Documents read

Three numbers tell you everything:

Pattern	Meaning
`docsExamined: 0`	Covered query — ideal
`docsExamined ≈ nReturned`	Good index
`docsExamined ≫ nReturned`	Index not selective enough
`COLLSCAN` with a large collection	Missing index
`SORT` stage present	Sort not served by an index

Reading 50,000 documents to return 20 is a partial index — the equality fields matched but the rest was filtered after fetching.

db.students.find({ schoolId: 1 })
           .sort({ name: 1 })
           .explain("executionStats")

A SORT stage means MongoDB sorted in memory. That fails outright above 100 MB:

Sort exceeded memory limit of 104857600 bytes

The fix is an index covering the sort, not allowDiskUse — which makes it slow rather than failing.

db.students.find({ schoolId: 1, className: "10th" }).hint({ schoolId: 1, className: 1 })

hint forces an index. Useful for testing which is better; avoid it in application code — it prevents the optimiser adapting as data changes.

Index costs

Indexes are not free.

Writes slow down. Every insert updates every index. Ten indexes means eleven writes per insert.

Storage grows. A compound index on four fields over a million documents is substantial.

Memory matters most. Indexes work well when they fit in RAM. Once the working set exceeds memory, every query hits disk and performance falls off a cliff.

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

{ name: "idx_school_class", accesses: { ops: 48291, since: ISODate("...") } }
{ name: "email_1", accesses: { ops: 0, since: ISODate("...") } }

ops: 0 over a representative period means the index only costs. Drop it.

Counters reset when the server restarts, so judge them over a real period — not ten minutes after a deployment.

Do not index every field. Index the queries you actually run, verified with explain.

Finding slow queries

db.setProfilingLevel(1, { slowms: 100 })

db.system.profile.find().sort({ ts: -1 }).limit(10).pretty()

db.system.profile.find({ millis: { $gt: 500 } })
                 .sort({ millis: -1 })
                 .limit(10)

Level	Records
`0`	Nothing
`1`	Operations slower than `slowms`
`2`	Everything — development only

Level 1 with a sensible threshold is the production setting. Level 2 writes a profile document per operation and is itself a performance problem.

db.system.profile.aggregate([
    { $group: { _id: "$command.filter", count: { $sum: 1 }, avgMillis: { $avg: "$millis" } } },
    { $sort: { count: -1 } },
    { $limit: 10 }
])

A query taking 50 ms and running 10,000 times a minute costs more than one taking 2 seconds and running hourly. Sort by total time, not by duration.

db.currentOp({ "secs_running": { $gt: 5 } })
db.killOp(opid)

For a query blocking everything right now.

Atlas provides the Performance Advisor, which suggests indexes from real traffic. Treat its suggestions as candidates, not instructions — applying every one produces overlapping indexes that cripple writes.

Aggregation basics

For reports, find is not enough.

db.examResults.aggregate([
    { $match: { schoolId: 1, examId: ObjectId("e1"), isAbsent: false } },
    { $group: {
        _id: "$className",
        count: { $sum: 1 },
        averageMarks: { $avg: "$marks" },
        highestMarks: { $max: "$marks" }
    }},
    { $sort: { averageMarks: -1 } }
])

A pipeline: each stage transforms the documents and passes them on.

Stage	Does
`$match`	Filter — put it first
`$group`	Aggregate
`$project`	Reshape
`$sort`	Order
`$limit` / `$skip`	Page
`$lookup`	Join another collection
`$unwind`	One document per array element
`$count`	Count

$match first is the most important rule. It is the only stage that can use an index, and only while it is at the start of the pipeline. Filtering after a $group means grouping every document and discarding most of the result.

// Wrong — groups everything, then filters
db.examResults.aggregate([
    { $group: { _id: "$className", avg: { $avg: "$marks" } } },
    { $match: { _id: "10th" } }
])

// Right
db.examResults.aggregate([
    { $match: { className: "10th" } },
    { $group: { _id: "$className", avg: { $avg: "$marks" } } }
])

db.students.aggregate([
    { $match: { schoolId: 1, status: "Active" } },
    { $lookup: {
        from: "examResults",
        localField: "_id",
        foreignField: "studentId",
        as: "results"
    }},
    { $project: {
        name: 1,
        rollNumber: 1,
        resultCount: { $size: "$results" },
        averageMarks: { $avg: "$results.marks" }
    }}
])

$lookup is a left outer join and it is expensive — it runs a query per input document unless the foreign field is indexed. Index studentId on examResults, or the pipeline scans that collection once per student.

That cost is exactly why the previous article duplicated studentName onto result documents: the report reads one collection with no lookup at all.

{ $group: {
    _id: "$className",
    total: { $sum: 1 },
    absent: { $sum: { $cond: [{ $eq: ["$isAbsent", true] }, 1, 0] } },
    passed: { $sum: { $cond: [{ $gte: ["$marks", 35] }, 1, 0] } }
}}

$cond gives conditional counts in one pass — the equivalent of SUM(CASE WHEN ...).

db.examResults.aggregate(pipeline).explain("executionStats")

Aggregations explain too. Check the first stage uses an index.

Diagnosing a slow query

Work in this order.

1. explain("executionStats"). COLLSCAN means no index. A SORT stage means an in-memory sort.

2. Compare totalDocsExamined with nReturned. A large gap means the index is not selective enough.

3. Check the prefix rule. An index on { schoolId, className } does nothing for a query on className alone.

4. Check for a SORT stage. Add the sort field to the index, in ESR order.

5. Check the aggregation's first stage. $match must be first.

6. Check $lookup foreign fields are indexed.

7. Check the working set fits in memory. A sudden cliff as data grows is usually this.

Symptom	Cause
`COLLSCAN`	No usable index
Index exists but unused	Prefix rule, type mismatch, or missing collation
`SORT` stage	Sort not covered by an index
Sort memory error	Same, on a large result
`docsExamined ≫ nReturned`	Index too broad
Slow aggregation	`$match` not first, or an unindexed `$lookup`
Writes slowing over time	Too many indexes
Sudden cliff	Working set exceeded RAM

A type mismatch also prevents index use, and it is easy to miss: a query passing a string against a numeric field matches nothing and reports a fast COLLSCAN — fast because it found nothing.

Errors you will hit

What you see	Cause	Fix
`COLLSCAN` in the explain plan	No usable index	Create one matching the query
Index exists but is not used	Field order wrong for the query	Compound index order matters
Writes got slower	Every index is maintained on write	Index only what you query
A sort still scans	Sort field not in the index	Include it
`E11000` on a unique index in a multi-tenant collection	Index not compound with the tenant	`{ schoolId: 1, rollNumber: 1 }`, unique

Read explain() before and after adding an index. COLLSCAN to IXSCAN is the proof it did something.

Common mistakes

No index on a frequently queried field
Indexing every field
Wrong field order in a compound index
Ignoring the prefix rule
Range before sort, forcing an in-memory sort
The tenant field not leading in a multi-tenant system
A redundant index that is a prefix of another
hint in application code
A partial index whose predicate the query omits
A collation index queried without the collation
Two array fields in one compound index
$match not first in an aggregation
$lookup on an unindexed foreign field
Profiling level 2 in production
Judging index usage over a few minutes
Never running explain

Practice

The course exercise is create and explain a basic index.

Insert 100,000 students with varied schools, classes and marks.
Query by rollNumber with no index. Run explain and record the stage, totalDocsExamined and time.
Add the index and re-run. Compare all three.
Create { schoolId: 1, className: 1, section: 1 }. Test queries on schoolId, schoolId + className, className alone, and section alone. Record which use the index.
Explain the prefix rule from what you observed.
Sort by name with no covering index over 200,000 documents. Record the SORT stage, then the memory-limit error.
Add an index covering the sort and confirm the stage disappears.
Build a query with equality, sort and range. Order the index ESR, then range-before-sort, and compare the plans.
Create an index covering a four-field projection with _id: 0. Confirm totalDocsExamined: 0.
Add _id: 1 to the projection and confirm the coverage is lost.
Create a partial index on status: "Active". Query with and without the predicate and compare.
Create a unique index on email with two documents missing it. Record the failure, then use sparse.
Create a collation index and query with and without the collation.
Create ten indexes. Time 10,000 inserts, drop them, and repeat.
Run $indexStats after exercising the collection and find an index with ops: 0.
Enable profiling at level 1 with slowms: 50. Run a slow query and find it in system.profile.
Write a class-average aggregation with $match last, then first. Compare executionTimeMillis.
Add a $lookup on an unindexed studentId, then index it and compare.

Exercises 4, 8 and 17 are the three that most change how you write queries.

You can now

Create indexes that measurably change a query plan
Read explain() and tell COLLSCAN from IXSCAN
Order compound index fields correctly
Say why every index slows writes
Make a unique index tenant-safe

Review questions

What is the prefix rule, and what does it mean for compound index design?
What does the ESR order optimise, and what happens if range comes before sort?
What does totalDocsExamined: 0 indicate?
Why must $match be the first stage of an aggregation?

Next: Application integration

Before you start​

Learning objective​

Topics​

Why index​

Creating indexes​

Compound indexes and field order​

The ESR rule​

Fewer, broader indexes​

Covered queries​

Index types​

Reading an explain plan​

Index costs​

Finding slow queries​

Aggregation basics​

Diagnosing a slow query​

Errors you will hit​

Common mistakes​

Practice​

You can now​

Review questions​