Without an index, MongoDB must examine every document in a collection to answer a query (a COLLSCAN). That’s fine for a thousand documents and painful for ten million. The right indexes make queries take milliseconds, while the wrong ones waste RAM and slow down writes.

Create and list indexes

db.orders.createIndex({ customerId: 1 })
db.orders.getIndexes()
db.orders.dropIndex("customerId_1")

1 is ascending and -1 descending. Direction matters only for compound indexes used for sorting. Every collection has an index on _id automatically.

Building an index on a large collection takes time and resources. Modern MongoDB builds indexes without blocking reads and writes for most of the process, but schedule big builds for quiet periods.

Index types

Type Example Use
Single field { email: 1 } Lookups on one field
Compound { customerId: 1, createdAt: -1 } Filters + sorts on several fields
Multikey { tags: 1 } on an array Query array elements (automatic for arrays)
Text { title: "text", body: "text" } Basic keyword search
TTL { createdAt: 1 }, { expireAfterSeconds: 86400 } Auto-delete old documents
Unique { email: 1 }, { unique: true } Enforce uniqueness
Partial { status: 1 }, { partialFilterExpression: { status: "open" } } Index only some documents
Hashed { userId: "hashed" } Hashed sharding keys
2dsphere { location: "2dsphere" } Geospatial queries

Compound indexes and the ESR rule

The order of fields in a compound index is crucial. Follow ESR:

  1. Equality fields first (status: "paid");
  2. Sort fields next (createdAt: -1);
  3. Range fields last (total: { $gt: 100 }).

For this query:

db.orders.find({ status: "paid", total: { $gt: 100 } }).sort({ createdAt: -1 })

the best index is:

db.orders.createIndex({ status: 1, createdAt: -1, total: 1 })

A compound index also serves queries on its prefixes: { status: 1, createdAt: -1, total: 1 } supports queries on status, and on status + createdAt, but not on createdAt alone.

Read explain() output

db.orders.find({ status: "paid" }).sort({ createdAt: -1 }).explain("executionStats")

Check these fields:

Field Good sign
winningPlan.stage IXSCAN (index scan), not COLLSCAN
totalKeysExamined Close to nReturned
totalDocsExamined Close to nReturned (0 for covered queries)
SORT stage present Bad: an in-memory sort; the index doesn’t cover the sort
executionTimeMillis Low

If totalDocsExamined is far larger than nReturned, the index isn’t selective enough, or the wrong one was chosen.

Covered queries

If all filtered and returned fields are in the index, MongoDB answers from the index alone, without reading documents:

db.users.createIndex({ email: 1, name: 1 })
db.users.find({ email: "[email protected]" }, { _id: 0, email: 1, name: 1 })

TTL indexes for automatic cleanup

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

A background task removes expired documents about every 60 seconds. It’s ideal for sessions, logs, and temporary tokens.

Don’t over-index

Each index:

  • uses RAM (indexes should fit in memory alongside the working set);
  • slows down every insert, update and delete;
  • uses disk.

Find indexes nobody uses:

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

Indexes with very low accesses.ops since the last restart are candidates for removal. Hide an index before dropping it, to test safely: db.orders.hideIndex("old_index_1").

Find slow queries

Enable the profiler for slow operations:

db.setProfilingLevel(1, { slowms: 100 })
db.system.profile.find().sort({ ts: -1 }).limit(5)

Slow queries are also logged in /var/log/mongodb/mongod.log. See MongoDB performance.

Quick reference

Task Command
Create db.c.createIndex({ a: 1, b: -1 })
List db.c.getIndexes()
Analyze query .explain("executionStats")
Usage stats db.c.aggregate([{ $indexStats: {} }])
Hide / drop hideIndex(), dropIndex()
Compound order Equality → Sort → Range

Related: installing MongoDB on Ubuntu and database indexing basics.

  • MongoDB
  • Database
  • Performance