Creating Indexes

An index in MongoDB is a small, sorted data structure that lets the database jump straight to the documents that match a query instead of checking every document in the collection. Without the right index, even a simple filter on a large collection forces a full collection scan, and that scan gets slower as the collection grows. Creating indexes deliberately, on the fields you actually query, sort, and filter by, is one of the highest-leverage things you can do for MongoDB performance. This lesson covers how to build single-field, compound, and unique indexes with createIndex(), how to confirm an index is actually being used with explain(), and the mistakes that quietly make indexes useless.

Overview: How Indexes Work

Every MongoDB collection automatically gets one index for free: an ascending index on _id, which is why looking up a document by its _id is always fast. Any other field you query regularly needs its own index, created explicitly.

Internally, most MongoDB indexes are B-tree structures. A B-tree stores index keys (the value of the indexed field, or fields, for every document) in sorted order, alongside a pointer to the location of the actual document. Because the keys are sorted, MongoDB’s query planner can use binary-search-like traversal to find matching keys in roughly logarithmic time, rather than reading every document linearly. This is the same fundamental idea as an index in the back of a textbook: instead of reading every page to find mentions of a term, you jump to the index, find the term already in alphabetical order, and go straight to the listed pages.

When you run a query, MongoDB’s query planner examines the available indexes and estimates which one (if any) would answer the query most cheaply. If no suitable index exists, it falls back to a collection scan (COLLSCAN), reading every document in storage order and testing each one against your filter. If a usable index exists, it performs an index scan (IXSCAN), walking only the relevant part of the B-tree. You can see exactly which one happened by calling .explain() on a query, and you should get in the habit of doing this on any query that runs against a collection of meaningful size.

Indexes are not free. Every index you add is maintained on every write: an insertOne, updateOne, or deleteOne that touches an indexed field has to update that index’s B-tree in addition to the document itself. More indexes mean faster reads on those fields but slower writes and more disk and RAM usage, since MongoDB tries to keep frequently used index pages in memory. Index creation is a deliberate trade-off, not something to do reflexively for every field.

Syntax

Indexes are created with createIndex(), called on the target collection:

db.collection.createIndex(
  { field1: 1, field2: -1 },
  {
    unique: false,
    sparse: false,
    name: "field1_1_field2_-1",
    expireAfterSeconds: 3600,
    partialFilterExpression: { field1: { $exists: true } }
  }
);

• keys document (first argument) — one or more fields to index, each mapped to 1 (ascending) or -1 (descending). For a single-field index the direction rarely matters; for compound and sorted queries it does.
• unique — if true, rejects any insert or update that would create a duplicate value for the indexed field(s). Defaults to false.
• sparse — if true, the index only includes documents that actually have the indexed field, skipping documents where it’s missing. Useful for optional fields.
• name — a custom name for the index. If omitted, MongoDB auto-generates one from the field names and directions (e.g. email_1).
• expireAfterSeconds — turns this into a TTL (time-to-live) index that automatically deletes documents a set number of seconds after the value in a date field; only valid on a single date field.
• partialFilterExpression — restricts the index to only documents matching a filter condition, keeping the index smaller than indexing every document.

Index builds are online by default in modern MongoDB: the collection remains available for reads and writes while the index is built in the background, so you generally don’t need to think about foreground vs. background builds the way older MongoDB versions required.

Examples

Example 1: A single-field index

Suppose db.users has a few hundred thousand documents and you frequently look users up by email. Without an index, here’s what happens:

db.users.find({ email: "amy.chen@example.com" }).explain("executionStats");

Output:

{
  queryPlanner: {
    winningPlan: { stage: "COLLSCAN", filter: { email: { "$eq": "amy.chen@example.com" } } }
  },
  executionStats: {
    nReturned: 1,
    totalDocsExamined: 300000,
    totalKeysExamined: 0,
    executionTimeMillis: 84
  }
}

The COLLSCAN stage and totalDocsExamined: 300000 confirm MongoDB read every document to find the one match. Now create an index on email:

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

Output:

"email_1"

createIndex() returns the name of the newly created (or already-existing) index. Re-running the same find().explain() now shows:

{
  queryPlanner: {
    winningPlan: { stage: "FETCH", inputStage: { stage: "IXSCAN", indexName: "email_1" } }
  },
  executionStats: {
    nReturned: 1,
    totalDocsExamined: 1,
    totalKeysExamined: 1,
    executionTimeMillis: 0
  }
}

IXSCAN plus totalDocsExamined: 1 shows MongoDB found the single matching key in the index and fetched only that one document — no scanning required.

Example 2: A compound index

Say db.orders stores customer orders, and a common query filters by customerId and sorts by orderDate:

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

Output:

"customerId_1_orderDate_-1"

This single compound index now serves queries that filter on customerId and sort by orderDate:

db.orders
  .find({ customerId: "cust_1029" })
  .sort({ orderDate: -1 });

The field order in a compound index matters enormously, and the rule of thumb is ESR: Equality, Sort, Range. Put fields you filter on with an exact match first, then fields you sort by, then fields you filter with a range ($gt, $lt, $in, etc.) last. That ordering lets MongoDB narrow to an exact equality match in the B-tree first, then walk the remaining sorted keys in order for the sort or range, without an extra in-memory sort step.

Example 3: A unique index

To guarantee no two users share an email address, create a unique index:

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

Output:

"email_1"

Now any attempt to insert a duplicate value is rejected at the database level, not just in application code:

db.users.insertOne({ email: "amy.chen@example.com", name: "Amy Chen (duplicate)" });

Output:

MongoServerError: E11000 duplicate key error collection: shop.users index: email_1 dup key: { email: "amy.chen@example.com" }

This is a real safety net: enforcing uniqueness in the database means it holds even if a bug in application code skips a validation check.

How It Works Step by Step

When createIndex() runs, MongoDB scans the existing documents in the collection once, builds the sorted B-tree structure for the specified field(s), and writes it to disk alongside the collection. On a large, already-populated collection this initial build can take real time and I/O, though the collection stays usable throughout since builds are online by default.

After that, every subsequent write updates the index incrementally: an insertOne adds a new key entry, a deleteOne removes one, and an updateOne that changes an indexed field removes the old key and inserts the new one. This is why write-heavy collections with many indexes see reduced write throughput — each write now touches N+1 data structures (the document plus each affected index) instead of just one.

On read, the query planner doesn’t blindly trust one index. For a query it hasn’t seen recently, it may run a few candidate plans in parallel for a short trial period and cache whichever wins, re-evaluating that cached plan periodically or whenever the collection’s structure changes significantly. This is why .explain() is the reliable way to check what’s actually happening, rather than assuming an index you created is the one being used.

Common Mistakes

Mistake 1: Wrong field order in a compound index. Putting a range/sort field before the equality field breaks the ESR rule and forces extra work:

// Wrong: orderDate (range/sort) placed before customerId (equality)
db.orders.createIndex({ orderDate: -1, customerId: 1 });

This index is still usable, but far less efficiently for a query that filters on customerId and sorts by orderDate, since MongoDB can’t narrow to a single customer’s keys first. Fix it by putting the equality field first:

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

Mistake 2: Assuming an index exists without checking. It’s easy to create an index in a dev database, forget to run the same command against production, and then wonder why a query is slow. Always confirm with .explain("executionStats") that the winning plan uses IXSCAN, not COLLSCAN, on any collection large enough to matter. You can also list every index on a collection directly:

db.orders.getIndexes();

Output:

[
  { v: 2, key: { _id: 1 }, name: "_id_" },
  { v: 2, key: { customerId: 1, orderDate: -1 }, name: "customerId_1_orderDate_-1" }
]

Mistake 3: Creating redundant or excessive indexes. A compound index on { customerId: 1, orderDate: -1 } already efficiently serves queries that filter on customerId alone, because a compound index also supports queries on a prefix of its fields. Adding a separate { customerId: 1 } index on top is pure waste — it duplicates coverage while adding write overhead and disk usage. Drop unused or redundant indexes:

db.orders.dropIndex("orderDate_-1_customerId_1");

Mistake 4: Adding a unique index to a collection that already has duplicates. createIndex() with unique: true will fail outright if existing documents already violate uniqueness — clean up the duplicate data first, then add the constraint.

Best Practices

• Index the fields you actually filter, sort, and join ($lookup) on — not every field, and not preemptively for hypothetical future queries.
• Follow the ESR rule (Equality, Sort, Range) when ordering fields in a compound index.
• Run .explain("executionStats") on any query against a collection with more than a few thousand documents, and confirm you see IXSCAN, not COLLSCAN.
• Remember that a compound index also serves queries on a leading prefix of its fields — don’t create a redundant single-field index that duplicates a prefix you already have.
• Use unique: true to enforce data integrity constraints at the database level, not just in application validation.
• Use sparse or partialFilterExpression to keep an index small when only a subset of documents have the field or match a useful condition.
• Periodically review indexes with db.collection.getIndexes() and drop ones that no query actually uses — check your database’s index usage stats if available.
• Build indexes on production during low-traffic windows when possible; online builds don’t block the collection, but they still consume I/O and CPU.

Practice Exercises

1. You have a db.products collection with fields sku, category, and price. Users frequently look up a product by exact sku. Create the index that would make this lookup use IXSCAN instead of COLLSCAN, then verify it with .explain().
2. A query filters db.orders by status: "shipped" and sorts by shippedAt descending. Design a compound index following the ESR rule, and write the find().sort() query it should serve.
3. Your db.users collection has a referralCode field that only about 10% of users have, and it must be unique when present. Create an index using both unique and sparse options that enforces this correctly.

Summary

• An index is a sorted B-tree structure that lets MongoDB jump to matching documents instead of scanning the whole collection.
• Every collection has a built-in index on _id; every other useful field needs an explicit createIndex() call.
• .explain("executionStats") reveals whether a query used IXSCAN (index) or COLLSCAN (full scan) — always check it on non-trivial collections.
• Compound indexes should order fields as Equality, then Sort, then Range (ESR), and also serve queries on any leading prefix of their fields.
• unique, sparse, partialFilterExpression, and expireAfterSeconds let you tailor an index to enforce constraints or save space.
• Indexes speed up reads but add overhead to every write and consume disk and memory — only index what you actually query.