How to Create and Analyze Indexes in MongoDB with explain()

Quick answer

Select the database, then run createIndex() on the collection. This example creates an ascending index on the email field:

use shop

db.customers.createIndex({ email: 1 })

Then check whether the query actually uses it:

db.customers.find({ email: "alex@example.com" })
  .explain("executionStats")

Look for IXSCAN in the execution plan. If COLLSCAN appears instead, MongoDB scanned the collection rather than using an index. Compare totalDocsExamined with nReturned, too. An efficient lookup will usually examine about as many documents as it returns.

One warning: Don’t create an index for every field. Indexes speed up reads, but they take storage and make inserts, updates, and deletes do more work. Build them around queries the application really runs.

What MongoDB indexes do

A MongoDB index keeps selected field values in an ordered structure, with pointers back to the collection documents. The query engine can then find matching records without checking every document one by one.

Without a suitable index, the query may fall back to a collection scan. That’s often fine for a small collection. As the collection grows, though, the cost can climb quickly.

What you’ll need

• Access to a MongoDB deployment through mongosh or a compatible database client
• Permission to create indexes on the target collection
• A representative query with realistic test data
• A recent backup or a tested recovery process before changing production indexes

Creating indexes in MongoDB

1. Create a single-field index

Set the field to 1 for ascending order or -1 for descending order:

db.customers.createIndex({ email: 1 })

MongoDB chooses an index name automatically, something like email_1. If you’d rather use a descriptive name, supply one yourself:

db.customers.createIndex(
  { email: 1 },
  { name: "customer_email_lookup" }
)

2. Create a unique index

A unique index blocks duplicate values. It’s a good fit for identifiers such as usernames or account email addresses:

db.customers.createIndex(
  { email: 1 },
  { unique: true, name: "unique_customer_email" }
)

If the existing documents already contain duplicate indexed values, the operation fails. Check the data first, then clean up any duplicates before adding the constraint.

3. Create a compound index

Compound indexes cover more than one field. Say an order screen filters by customer and status, then sorts the newest orders first:

db.orders.find({
  customerId: ObjectId("64f000000000000000000001"),
  status: "paid"
}).sort({ createdAt: -1 })

This index would suit that query:

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

Field order matters here. One common rule is to put equality fields first, sort fields next, and range fields after those. It’s a guideline, not a substitute for testing. The application’s real query patterns and the results from explain() should decide the final design.

Checking a query with explain()

Run explain() in executionStats mode to see the selected plan and what MongoDB did while executing it:

db.orders.find({
  customerId: ObjectId("64f000000000000000000001"),
  status: "paid"
}).sort({ createdAt: -1 })
  .explain("executionStats")

Start with these fields and stages:

Seeing IXSCAN doesn’t automatically mean the query is efficient. A poorly matched index may still inspect thousands of keys to return only a handful of documents. The examined counts, compared with nReturned, tell the fuller story.

Comparing performance before and after indexing

1. Run the representative query with explain("executionStats").
2. Record nReturned, totalKeysExamined, totalDocsExamined, and executionTimeMillis.
3. Create the proposed index.
4. Run the same query again and compare the plan.
5. Test other key queries and write-heavy workloads before rolling out the index more broadly.

executionTimeMillis can move around because of caching, server load, and dataset size. Examined-document and examined-key counts are generally steadier indicators. Even so, check latency under realistic load before making the call.

Listing and removing indexes

To list every index on a collection, run:

db.orders.getIndexes()

You can remove an index by name:

db.orders.dropIndex("customerId_1_status_1_createdAt_-1")

Or remove it with its key specification:

db.orders.dropIndex({
  customerId: 1,
  status: 1,
  createdAt: -1
})

Don’t drop the default _id index. And before deleting any other index, make sure it isn’t supporting a less obvious query, a sort, a uniqueness rule, or an operational task. Those dependencies can be easy to miss.

Common indexing mistakes

• Indexing every field: More indexes mean greater storage use and slower write operations.
• Ignoring compound field order: The fields may all be present, but the wrong order can keep the index from supporting the intended filter and sort.
• Testing only with tiny collections: MongoDB may sensibly choose a collection scan when nearly all the documents fit in memory.
• Judging only by execution time: A cached query can look fast while examining far too much data.
• Creating duplicate indexes: Check getIndexes() before adding another variation.
• Using low-selectivity fields by themselves: An index on a field with only a few repeated values may not help much.

MongoDB index practices that hold up

• Begin with production query shapes, rather than designing from the collection schema alone.
• Use compound indexes for filters and sorts that commonly run together.
• Weigh read gains against write latency and index storage costs.
• Check execution plans again as data volume and value distribution change.
• Avoid redundant indexes with overlapping prefixes and options unless there’s a clear reason for them.
• Whenever possible, test index changes in staging with production-like data.

Verification checklist

• The query returns the correct records.
• The winning plan includes IXSCAN where you expect an index.
• totalDocsExamined stays reasonably close to nReturned.
• There’s no avoidable blocking SORT stage.
• Key insert and update operations still meet acceptable latency limits.