MongoDB Index Design

MongoDB indexes are B-trees over field paths. Without a matching index a query performs a collection scan, which on a large collection also evicts the working set from cache.

Compound Indexes

The order of fields follows the ESR rule: Equality, then Sort, then Range.

// Query: find a customer's recent paid orders above a value.
db.orders.find({ customerId: cid, status: "paid", totalCents: { $gt: 5000 } })
         .sort({ createdAt: -1 })
         .limit(20);
 
// Equality fields, then the sort field, then the range field.
db.orders.createIndex({ customerId: 1, status: 1, createdAt: -1, totalCents: 1 });

Putting the range field before the sort field forces an in-memory sort, which fails outright once it exceeds the sort memory limit unless the query can use an index or allow disk use.

Like other B-tree systems, a compound index serves any prefix of its fields — {a: 1, b: 1, c: 1} serves queries on a, on a, b, and on a, b, c, but not on b alone.

Sort direction matters for multi-field sorts: an index on {a: 1, b: -1} supports sorting by a ascending with b descending, and its exact inverse, but not a ascending with b ascending.

Covered Queries

If every field a query needs — filter, sort and projection — is in the index, MongoDB answers it from the index alone and never reads the documents.

db.orders.createIndex({ customerId: 1, createdAt: -1, status: 1, totalCents: 1 });
 
// Covered: _id must be excluded explicitly, since it is returned by default.
db.orders.find(
  { customerId: cid },
  { _id: 0, createdAt: 1, status: 1, totalCents: 1 }
).sort({ createdAt: -1 });

Confirm with explain(): the winning plan should show IXSCAN with no FETCH stage.

db.orders.find({ customerId: cid }, { _id: 0, status: 1 }).explain("executionStats");

Read totalKeysExamined, totalDocsExamined and nReturned. Docs examined far exceeding documents returned means the index is not selective enough; docs examined at zero means the query was covered.

Specialised indexes

// Partial: index only the subset that is queried.
db.orders.createIndex(
  { createdAt: -1 },
  { partialFilterExpression: { status: "pending" } }
);
 
// TTL: delete documents automatically after a period.
db.sessions.createIndex({ lastSeenAt: 1 }, { expireAfterSeconds: 3600 });
 
// Unique, with a partial filter so it applies only where the field exists.
db.users.createIndex(
  { email: 1 },
  { unique: true, partialFilterExpression: { email: { $exists: true } } }
);
 
// Text and wildcard.
db.articles.createIndex({ title: "text", body: "text" });
db.events.createIndex({ "attributes.$**": 1 });

Multikey indexes

An index on an array field indexes every element. This works well and has two consequences: an index entry per array element per document, and no compound index may contain more than one array field. Large arrays inflate index size quickly.

Finding index problems

// Usage since the last restart.
db.orders.aggregate([{ $indexStats: {} }]);
 
// All indexes and their sizes.
db.orders.stats().indexSizes;
 
// Queries that were slow, from the profiler.
db.setProfilingLevel(1, { slowms: 100 });
db.system.profile.find({ millis: { $gt: 100 } }).sort({ ts: -1 }).limit(10);

An index with accesses.ops of zero after a full business cycle is a candidate for removal — remembering that statistics reset on restart and that monthly jobs look unused for 29 days.