How to Create an Index in MongoDB (Without Locking Your Collection)

The command is one line. Everything worth knowing is in the options you pass it and the check you run afterwards.

db.orders.createIndex({ customerId: 1 });

That builds an ascending single-field index on customerId. What follows is the rest of what the one-liner does not tell you.

Key takeaways#

• createIndex() is idempotent — running it again with identical options is a no-op, so it is safe in a migration that reruns.
• 1 is ascending, -1 descending. For a single-field index the direction is irrelevant; MongoDB walks either way.
• Since MongoDB 4.2, index builds on a replica set use an optimised build that holds an exclusive lock only briefly at the start and end, not for the whole build.
• Name your indexes. The generated name is derived from the keys and becomes unusable past a few fields.
• Creating an index proves nothing. explain('executionStats') proves it is used.

The basic forms#

Single field.

db.orders.createIndex({ customerId: 1 });

Compound, where field order decides everything — equality fields, then sort fields, then range fields:

db.orders.createIndex({ customerId: 1, createdAt: -1 });

Unique, which enforces a constraint as well as building an index:

db.users.createIndex({ email: 1 }, { unique: true });

Text, for language-aware search across string fields:

db.articles.createIndex({ title: 'text', body: 'text' });

A collection can hold only one text index, though it may cover several fields — a limit people usually discover on their second one.

Naming, and why the default is not good enough#

MongoDB derives a name from the keys and directions: { customerId: 1, createdAt: -1 } becomes customerId_1_createdAt_-1. Readable at two fields, unreadable at five, and capped — the full namespace has a length limit that a wide compound index on long field names can genuinely exceed.

db.orders.createIndex(
  { customerId: 1, createdAt: -1, total: 1 },
  { name: 'orders_customer_recent' },
);

A stable name is also what dropIndex('orders_customer_recent') needs, and what shows up in $indexStats when you are working out which indexes are dead weight.

Building on a live collection#

The old { background: true } option is deprecated and ignored on any currently supported server. Since MongoDB 4.2 there is one build process for replica sets: it takes an exclusive lock at the beginning and end of the build, and yields between, so reads and writes proceed for the bulk of the work.

That does not make an index build free. It still consumes I/O and memory proportional to the collection, and the memory it may use per build is bounded by maxIndexBuildMemoryUsageMegabytes — exceeding it spills to disk and slows the build considerably.

For a large production collection, the safe pattern is a rolling build: build the index on each secondary in turn with that member removed from the replica set, then step down the primary and repeat. It trades a longer operation for near-zero impact on the primary. MongoDB's manual documents the exact sequence, and it is worth following precisely rather than improvising.

For anything smaller, build it in a maintenance window and watch db.currentOp().

Partial and sparse indexes#

A partial index covers only documents matching a filter. It is smaller, faster to build, and cheaper to maintain:

db.orders.createIndex(
  { createdAt: -1 },
  {
    name: 'orders_recent_open',
    partialFilterExpression: { status: 'open' },
  },
);

The catch is that the query planner only uses a partial index when it can prove the query is entirely within the filter. A query for status: 'open' uses it; a query with no status predicate cannot, even if every matching document happens to be open.

Sparse indexes skip documents missing the field. Partial indexes with { field: { $exists: true } } do the same thing and more, so treat sparse as legacy.

A unique partial index is the standard way to say "unique among active records":

db.users.createIndex(
  { email: 1 },
  { unique: true, partialFilterExpression: { deletedAt: null } },
);

TTL indexes#

A TTL index deletes documents automatically once a date field passes an age. Sessions, verification tokens, ephemeral job records:

db.sessions.createIndex({ expiresAt: 1 }, { expireAfterSeconds: 0 });

Two properties catch people out. The indexed field must be a BSON date — a Unix timestamp stored as a number is ignored entirely, with no error. And deletion runs on a background task roughly every 60 seconds, so a document can outlive its expiry by up to a minute. TTL is for cleanup, not for enforcing an access deadline.

With expireAfterSeconds: 0, the field itself is the exact expiry time, which is usually what you want.

Confirming the index is used#

Building an index and assuming the query planner picked it is the step where this goes wrong. Ask:

db.orders
  .find({ customerId: 'c_8812' })
  .sort({ createdAt: -1 })
  .explain('executionStats');

Three things to read:

• winningPlan.stage — IXSCAN means indexed, COLLSCAN means a full collection scan.
• A SORT stage — its presence means the index did not supply the ordering and MongoDB sorted in memory, which fails outright above 32MB.
• totalKeysExamined vs nReturned — near each other means selective. Far apart means the index is being scanned rather than seeked.

If the planner ignores a new index, the usual cause is field order rather than a missing index. That is the ESR rule, and it is worth reading before adding a second index to compensate for the first.

Finding indexes nobody uses#

Every index is paid for on every write to the collection. $indexStats reports how often each has been used since the server last started:

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

An index with accesses.ops at or near zero, after a representative period of real traffic, is a candidate for removal — with two exceptions. A unique index is a constraint whether or not queries use it. And the _id index cannot be dropped.

db.orders.dropIndex('orders_customer_recent');

A checklist for adding one#

1. Write the query first; index design follows access patterns.
2. Order compound keys equality, sort, range.
3. Give it an explicit name.
4. Consider partialFilterExpression if the query always carries a filter.
5. Build in a window, or roll it through the replica set for a large collection.
6. Prove it with explain('executionStats').
7. Drop any existing index that is now a prefix of the new one.

Step 6 is the one worth doing carefully. explain('executionStats') returns a deeply nested document, and the JSON Formatter makes the difference between IXSCAN and COLLSCAN findable without scrolling.