Indexes

Creating indexes

_id is indexed automatically and cannot be dropped. Everything else is on you. A collection holds at most 64 indexes.

db.orders.createIndex({ placed_at: -1 })
db.orders.createIndex({ status: 1, placed_at: -1 }, { name: "status_recent" })
db.users.createIndex({ email: 1 }, { unique: true })
db.orders.createIndexes([ { key: { ref: 1 } }, { key: { "items.sku": 1 } } ])

db.orders.getIndexes()
db.orders.dropIndex("status_recent")
db.orders.dropIndex({ status: 1, placed_at: -1 })   // by key pattern
db.orders.dropIndexes()                             // all but _id

Index builds on a replica set take a brief exclusive lock at the start and end only, so background no longer means anything. Passing it is harmless and marks your snippet as pre-4.2.

Compound indexes and the ESR rule

Field order in a compound index decides which queries it serves. The rule is ESR: Equality, Sort, Range.

1. Equality fields first, the ones tested with $eq or a bare value.
2. Sort fields next, in sort order.
3. Range fields last, $gt $lt $in $ne and friends.

// Query: equality on status, sort by placed_at desc, range on total
db.orders.find({ status: "paid", total: { $gt: 100 } })
         .sort({ placed_at: -1 })

// ESR index
db.orders.createIndex({ status: 1, placed_at: -1, total: 1 })

Put the range before the sort and the index stops supplying order, so the server adds a blocking SORT stage on top of the scan. That single reordering is the most common cause of a "why is my sort slow" ticket.

The leftmost prefix rule

A compound index is usable for any leftmost prefix of its keys, never for a non-prefix subset. An index on {a: 1, b: 1, c: 1} covers a, (a, b), and (a, b, c).

Direction only matters for sorting, and only relatively. A B-tree scans backwards, so {a: 1, b: -1} also serves sort({a: -1, b: 1}) but not sort({a: 1, b: 1}). Because of the prefix rule, {a: 1, b: 1} makes a separate {a: 1} index redundant, so drop it.

Index types

db.posts.createIndex({ title: "text", body: "text" }, { weights: { title: 10 } })
db.posts.find({ $text: { $search: "\"index scan\" -legacy" } },
              { score: { $meta: "textScore" } })
        .sort({ score: { $meta: "textScore" } })

db.places.createIndex({ loc: "2dsphere" })
db.places.find({ loc: { $near: {
  $geometry: { type: "Point", coordinates: [-9.14, 38.72] },
  $maxDistance: 2000
} } })
db.places.find({ loc: { $geoWithin: { $centerSphere: [[-9.14, 38.72], 0.01] } } })

db.products.createIndex({ "attrs.$**": 1 })     // any attrs.* field
db.sessions.createIndex({ last_seen: 1 }, { expireAfterSeconds: 1800 })

Text search supports quoted phrases and -term negation but has no relevance tuning beyond weights, no stemming choices past default_language, and one index per collection. On Atlas, $search is the real answer. TTL deletion is done by a single-threaded background task that wakes roughly every 60 seconds, so documents outlive their expiry by up to a minute, longer under load. expireAfterSeconds: 0 means "expire at the value of the indexed date", which is how you schedule per-document deletion.

Multikey indexes

Indexing an array field produces a multikey index automatically, with one index entry per element. You do not ask for it and you cannot turn it off.

A big array means a big index. A document with a 1,000-element array contributes 1,000 keys, so array size drives index size directly.

Unique, partial, and sparse

unique alone treats a missing field as null, so a second document without the field collides. The classic fix was sparse, but partialFilterExpression is strictly better.

// Many documents may omit email, at most one may hold a given email
db.users.createIndex({ email: 1 },
  { unique: true, partialFilterExpression: { email: { $type: "string" } } })

// Only index the rows you query
db.orders.createIndex({ placed_at: -1 },
  { partialFilterExpression: { status: "pending" } })

// One active subscription per user
db.subs.createIndex({ user_id: 1 },
  { unique: true, partialFilterExpression: { active: true } })

// Case-insensitive unique email
db.users.createIndex({ email: 1 },
  { unique: true, collation: { locale: "en", strength: 2 } })

The planner uses a partial index only when it can prove the query implies the index filter, so query with the literal { status: "pending" }, not a variable that is sometimes something else. A collation index is only used by a query carrying the same collation.

Reading explain()

Three verbosity levels. queryPlanner (the default) shows the chosen plan, executionStats actually runs it and reports counters, allPlansExecution adds the rejected candidates.

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

db.orders.explain("executionStats")
  .aggregate([{ $match: { status: "paid" } }, { $group: { _id: "$region" } }])

The four numbers that matter, all under executionStats:

// A healthy indexed query
// { nReturned: 20, totalKeysExamined: 20, totalDocsExamined: 20,
//   executionStages: { stage: 'FETCH', inputStage: { stage: 'IXSCAN', ... } } }

// The shape you are hunting for
// { nReturned: 20, totalKeysExamined: 0, totalDocsExamined: 2400000,
//   executionStages: { stage: 'COLLSCAN' } }

totalKeysExamined divided by nReturned is the selectivity ratio. At 1 the index is doing exactly the right work. At 1,000 you are scanning an index to throw almost all of it away, which usually means a missing leading equality field.

Maintenance

Hide an index before you drop it. A hidden index is still maintained, so unhiding is instant, while dropping and rebuilding a large index is not.

db.orders.hideIndex("status_recent")     // 4.4+, planner ignores it
// run your workload, watch for regressions
db.orders.unhideIndex("status_recent")   // instant rollback
db.orders.dropIndex("status_recent")     // only once you are sure

// Which indexes has nobody used since the last restart?
db.orders.aggregate([{ $indexStats: {} }])
  .forEach(i => print(i.name, i.accesses.ops))

$indexStats counters reset when mongod restarts, so judge an index on a full business cycle rather than an hour. Unused indexes are not free: every one of them is extra work on every insert, update, and delete.