MongoDB Indexes

Make queries fast with indexes: create single-field and compound indexes, follow the ESR rule for field order, enforce uniqueness, index only some documents with partial indexes, expire data with TTL indexes, and check index use with explain.

Lesson 11 of 43Intermediate17 min read

Without an index, MongoDB answers a query by reading every document in the collection and checking each one: a collection scan. With a hundred documents nobody notices. With ten million, a simple "find this user's orders" can take seconds and use a lot of memory and disk, slowing everything else down.

An index is a sorted structure that maps field values to the documents containing them, like the index at the back of a book. With an index on customerId, MongoDB jumps straight to that customer's orders. Indexes also make sorting fast, because the data is already in order, and unique indexes stop duplicate values such as two accounts with the same email.

Indexes have a cost: each one uses memory and disk, and every insert and update must update it too. So the skill is choosing the right indexes for the queries your app actually runs. In this lesson you'll create single-field, compound, unique, partial and TTL indexes, learn how the order of fields in a compound index matters, and check with explain that your queries use them.

Creating and listing indexes

MongoDB Shell
db.orders.createIndex({ customerId: 1 })              // ascending
db.orders.createIndex({ createdAt: -1 })              // descending
db.orders.getIndexes()                                // list them
db.orders.dropIndex('createdAt_-1')                   // remove one by name
  • Every collection has an index on _id automatically.
  • 1 and -1 set the order. For single-field indexes the direction rarely matters, because MongoDB can walk an index both ways.
  • Indexes on array fields are multikey: each element is indexed, so { tags: 'gift' } can use an index on tags.
  • The default name is built from the fields, such as customerId_1.

Compound indexes and the ESR rule

A compound index covers several fields: { status: 1, createdAt: -1 }. It supports queries on a prefix of its fields: status alone, or status and createdAt, but not createdAt alone.

Order the fields with the ESR rule:

  1. Equality fields first: fields matched exactly, such as status: 'paid'.
  2. Sort fields next: fields in .sort(), such as createdAt.
  3. Range fields last: fields with $gt, $lt or $in, such as total: { $gt: 100 }.

For find({ status: 'paid', total: { $gt: 100 } }).sort({ createdAt: -1 }), the best index is { status: 1, createdAt: -1, total: 1 }.

Special kinds of index

OptionEffectExample
unique: trueRejects duplicate valuescreateIndex({ email: 1 }, { unique: true })
partialFilterExpressionIndexes only documents matching a filter{ partialFilterExpression: { status: 'active' } }
sparse: trueSkips documents without the fieldOlder alternative to partial indexes
expireAfterSecondsTTL: deletes documents after a timecreateIndex({ createdAt: 1 }, { expireAfterSeconds: 86400 })
Text, geospatial, wildcardSearch words, locations, or unknown fieldsSee the text search lesson

A covered query is answered entirely from an index, without reading documents, when the filter and projection use only indexed fields and _id is excluded.

MongoDB Performance Syntax

Index commands

db.c.createIndex({ field: 1 })
db.c.createIndex({ status: 1, createdAt: -1 })
db.c.createIndex({ email: 1 }, { unique: true })
db.c.createIndex({ sku: 1 }, { partialFilterExpression: { active: true } })
db.c.createIndex({ expiresAt: 1 }, { expireAfterSeconds: 0 })
db.c.getIndexes()
db.c.dropIndex('status_1_createdAt_-1')
db.c.find({ status: 'paid' }).explain('executionStats')
db.c.aggregate([{ $indexStats: {} }])

MongoDB Performance Summary

  • Without an index, queries scan the whole collection; indexes let MongoDB jump to matching documents.
  • createIndex({ field: 1 }) creates an index; getIndexes lists them; _id is always indexed.
  • Compound indexes support queries on their prefix; order fields by Equality, Sort, Range.
  • Unique, partial, sparse and TTL options enforce uniqueness, index subsets and expire data.
  • Indexes cost memory and slow writes, so create the ones your queries need and check them with explain.

MongoDB Performance Examples

Each example shows the code, the output it produces and what each part does.

Example 1

An index turns a scan into a lookup

MongoDB Shell
const docs = []
for (let i = 0; i < 5000; i++) docs.push({ _id: i, customerId: i % 500, total: i % 97 })
db.orders.insertMany(docs).acknowledged

function plan() {
  const e = db.orders.find({ customerId: 42 }).explain('executionStats')
  return `${e.queryPlanner.winningPlan.stage === 'COLLSCAN' ? 'COLLSCAN' : 'IXSCAN'}, examined ${e.executionStats.totalDocsExamined} docs, returned ${e.executionStats.nReturned}`
}
plan()
db.orders.createIndex({ customerId: 1 })
plan()
db.orders.getIndexes().map(i => i.name)
Output
true
COLLSCAN, examined 5000 docs, returned 10
customerId_1
IXSCAN, examined 10 docs, returned 10
[ '_id_', 'customerId_1' ]
Line-by-line explanation
  1. Lines 1 to 3Five thousand orders spread across 500 customers.
  2. Line 6explain('executionStats') reports the plan MongoDB chose and how much work it did.
  3. Line 9Without an index, every document is examined to find 10 matches.
  4. Lines 10 to 11With an index on customerId, MongoDB examines only the 10 matching documents.
  5. Line 12_id has an index automatically; createIndex added the second one.
Example 2

Compound index order and sorting

MongoDB Shell
for (let i = 0; i < 2000; i++) db.orders.insertOne({ status: ['paid', 'pending', 'refunded'][i % 3], createdAt: new Date(2026, 0, 1 + (i % 60)), total: i % 250 })
db.orders.createIndex({ status: 1, createdAt: -1, total: 1 })

function stats(query, sort) {
  const e = db.orders.find(query).sort(sort).explain('executionStats')
  const usedSortStage = JSON.stringify(e.queryPlanner.winningPlan).includes('"SORT"')
  return `examined ${e.executionStats.totalKeysExamined} keys, in-memory sort: ${usedSortStage}`
}
stats({ status: 'paid', total: { $gt: 200 } }, { createdAt: -1 })
stats({ total: { $gt: 200 } }, { createdAt: -1 })
Output
status_1_createdAt_-1_total_1
examined 152 keys, in-memory sort: false
examined 0 keys, in-memory sort: true
Line-by-line explanation
  1. Line 2Equality (status), then sort (createdAt), then range (total): the ESR order.
  2. Line 9The index finds paid orders already sorted by date, so no separate sort step is needed.
  3. Line 10Without status in the query, the index prefix can't be used, so MongoDB scans the collection and sorts in memory.
Example 3

Unique, partial and TTL indexes

MongoDB Shell
db.users.createIndex({ email: 1 }, { unique: true })
db.users.insertOne({ email: 'ada@example.com' }).acknowledged
try {
  db.users.insertOne({ email: 'ada@example.com' })
} catch (e) {
  print(e.message.split(' collection')[0])
}

db.coupons.createIndex({ code: 1 }, { unique: true, partialFilterExpression: { active: true } })
db.coupons.insertMany([{ code: 'SPRING', active: false }, { code: 'SPRING', active: false }, { code: 'SPRING', active: true }]).acknowledged

db.sessions.createIndex({ lastSeen: 1 }, { expireAfterSeconds: 1800 })
db.sessions.getIndexes().find(i => i.name === 'lastSeen_1').expireAfterSeconds
Output
email_1
true
E11000 duplicate key error
code_1
true
lastSeen_1
1800
Line-by-line explanation
  1. Line 1A unique index stops two users sharing an email.
  2. Line 4The second insert fails with the E11000 duplicate key error.
  3. Line 9A partial unique index applies only to active coupons, so old inactive codes can repeat.
  4. Line 10Two inactive SPRING coupons are allowed alongside one active one.
  5. Line 12The TTL index removes sessions 30 minutes after their lastSeen time.

Common MongoDB Performance Mistakes

Compound fields in the wrong order

Don't
db.orders.createIndex({ createdAt: -1, status: 1 })   // for find({ status }).sort({ createdAt: -1 })
Do
db.orders.createIndex({ status: 1, createdAt: -1 })   // equality first, then sort

An index on every field

Don't
// 15 indexes on a busy collection: every insert updates all 15, and memory fills up
Do
// a few compound indexes chosen for the real queries, checked with explain and $indexStats

Checking uniqueness in application code

Don't
if (!db.users.findOne({ email })) db.users.insertOne({ email })   // two sign-ups at once can both pass
Do
db.users.createIndex({ email: 1 }, { unique: true })               // the database enforces it

MongoDB Performance in Real Projects

Indexes in production

Teams design indexes alongside new features, add them with createIndex during deployment, and watch slow query logs and Atlas's Performance Advisor, which suggests missing indexes. Unique indexes protect emails, usernames and order numbers. Partial indexes keep indexes small on big collections where only active or recent documents are queried, and TTL indexes clean up sessions and temporary data automatically.

MongoDB Performance FAQ

How many indexes can a MongoDB collection have?

A collection can have at most 64 indexes, including the automatic _id index. You should never need that many: every index slows down inserts and updates and uses memory and disk. A handful of well-designed compound indexes usually serves an application's queries better than dozens of single-field ones.

Why does creating a unique index fail with a duplicate key error?

The collection already contains documents with the same value in that field, so MongoDB refuses to build an index that would be violated from the start. Find the duplicates with an aggregation such as $group on the field plus $match: { count: { $gt: 1 } }, fix or merge them, then create the index again. Documents missing the field also count as null, so two of them clash too unless you use a partial index.

Does creating an index lock the collection?

Not for the whole build. Since MongoDB 4.2, index builds take an exclusive lock only briefly at the start and the end, and reads and writes continue in between. Building an index on a large collection still uses CPU, memory and disk, so schedule big builds for quiet periods on busy production systems.

How do I test whether an index is safe to drop?

Hide it first with db.orders.hideIndex('createdAt_-1'). The query planner then ignores it, but MongoDB keeps maintaining it, so unhideIndex brings it back instantly if queries slow down. Check $indexStats beforehand too: an index whose accesses.ops stays at zero over a normal period of traffic probably isn't used.

Does the order of fields in a compound index matter?

Yes, a lot. An index on { status: 1, createdAt: -1 } supports queries on status alone or on status and createdAt, but not on createdAt alone, because it's sorted by status first. Order fields by the ESR rule: equality fields, then sort fields, then range fields.

MongoDB Performance Exercises

Answer each one and check it instantly. If you get stuck, open the hint before the answer.

Fill in the blankseasy

Complete the command that creates an index on email.

db.users.({ email: 1 })
Multiple choiceeasy

Which field is always indexed automatically?

Multiple choicemedium

An index is { status: 1, createdAt: -1 }. Which query can use it efficiently?

Multiple choicemedium

What does a unique index do?

Multiple choicehard

In the ESR rule, where do range fields go in a compound index?

MongoDB Performance Quiz

5 questions to check what you learned.

  1. What is a collection scan?

    • A backup
    • An index type
    • A security check
    • Reading every document to find matches, because no suitable index exists

  2. What's a downside of indexes?

    • They make reads slower
    • They delete data
    • They use memory and disk, and slow down writes
    • They only work on strings

  3. What does partialFilterExpression do?

    • Indexes only documents that match a condition
    • Filters query results
    • Removes old documents
    • Creates a text index

  4. What is a multikey index?

    • An index on an array field, with an entry for each element
    • An index with many names
    • A compound index
    • A unique index

  5. What is a covered query?

    • A query with a projection
    • A query inside a transaction
    • A query answered entirely from an index, without reading the documents
    • A query that returns no results

Related MongoDB topics

21Performance & explainFind and fix slow MongoDB queries: read explain plans, compare keys and documents examined with results returned, spot collection scans and in-memory sorts, build covered queries, use the profiler and slow query log, and apply a repeatable tuning process.05Projection & SortingReturn only the fields you need with projections, order results with sort, page through them with limit and skip or range-based paging, and slice arrays in the results.17Text SearchAdd search to your app with MongoDB: create text indexes, search words and phrases with $text, exclude terms, sort by relevance score, weight important fields, compare with regex and collation, and know when to move to Atlas Search.
Esc
↑↓ to navigateEnter to open