Index Properties

An index option changes which documents get a key, or what happens when two produce the same one. A unique index rejects the duplicate, and is MongoDB 1,815 's only uniqueness mechanism.

A unique index, and why it fails on an optional field

db.orders.createIndex({ orderNo: 1 }, { unique: true });
db.orders.insertOne({ orderNo: 100000, note: 'dup' });
db.orders.createIndex({ coupon: 1 }, { unique: true, name: 'coupon_u' });

Output

E11000 duplicate key error ... index: orderNo_1 dup key: { orderNo: 100000 }
E11000 duplicate key error ... index: coupon_u dup key: { coupon: null }

The second failure is the classic trap: only 4,009 orders carry a coupon, a missing field is indexed as null, and 95,991 documents collide on one key. A partial index fixes it by indexing only documents matching a filter — and partials pay off beyond uniqueness. An order queue reads pending orders constantly, the rest almost never.

Partial indexes: unique on an optional field, and sized to one query

db.orders.createIndex({ coupon: 1 }, { name: 'coupon_partial', unique: true,
  partialFilterExpression: { coupon: { $exists: true } } });
db.orders.createIndex({ createdAt: 1 }, { name: 'open_orders',
  partialFilterExpression: { status: 'pending' } });
db.orders.createIndex({ createdAt: 1 }, { name: 'createdAt_all' });
const q = { status: 'pending', createdAt: { $gt: new Date('2025-10-01') } };
for (const n of ['open_orders', 'createdAt_all']) {
  const s = db.orders.find(q).hint(n).explain('executionStats').executionStats;
  print(`${n}: bytes=${db.orders.stats().indexSizes[n]} keys=${s.totalKeysExamined}` +
    ` returned=${s.nReturned} ms=${s.executionTimeMillis}`); }

Output

open_orders: bytes=258048 keys=2518 returned=2518 ms=5
createdAt_all: bytes=1142784 keys=12537 returned=2518 ms=25

A quarter of the bytes and a fifth of the keys, because open_orders holds only the 20,056 pending orders. The catch: the planner uses a partial index only when it can prove the query matches the filter, so dropping status: 'pending' leaves it unused. A sparse index is the blunt ancestor, skipping documents where the field is absent; on coupon both come to 69,632 bytes, but sparse says no more than "field exists", and a sort hinted at it silently returns 4,009 of 100,000 documents.

A date index can also carry a deletion schedule: see TTL Indexes and Expiring Data. Hiding, the last option here, makes the planner ignore an index while the server still maintains it, so you can rehearse a drop without paying for a rebuild if you were wrong.

Hiding an index to rehearse dropping itPython

db.orders.hideIndex('region_total');
const s = db.orders.find({ region: 'apac', total: { $gte: 4500 } },
  { _id: 0, region: 1, total: 1 }).explain('executionStats').executionStats;
print(s.executionStages.stage + ' docs=' + s.totalDocsExamined);

Output

{ hidden_old: false, hidden_new: true, ok: 1 }
PROJECTION_SIMPLE docs=16804

The covered query of Covered Queries loses its cover: with region_total invisible the planner picks region_createdAt instead and fetches all 16,804 apac orders. unhideIndex restores it in milliseconds, which rebuilding a large index would not. _id_ cannot be hidden.

MENU