Intermediate22 min read

Indexing & Performance

Create single-field, compound, and text indexes; optimize queries with explain plans.

Index Types

Single-field indexes speed equality and range queries on one field. Compound indexes support queries on multiple fields, with field order mattering critically—put equality filters first, then sort fields, then range filters.

Specialized indexes include multikey (arrays), text, geospatial (2dsphere), and hashed indexes. Choose the index type that matches your query operators, not just the fields you store.

The default _id index exists on every collection automatically
Unique indexes enforce uniqueness and reject duplicate inserts/updates
TTL indexes expire documents after a specified seconds-since-field interval

db.users.createIndex({ email: 1 }, { unique: true })
db.orders.createIndex({ customerId: 1, createdAt: -1 })
db.stores.createIndex({ location: "2dsphere" })

Creating and Managing Indexes

Create indexes in production during low-traffic windows on large collections, because index builds consume CPU and I/O. Use createIndex with background-friendly options appropriate to your server version.

Review existing indexes with getIndexes and drop unused ones—every index slows writes and consumes RAM. Partial indexes index only documents matching a filter, saving space when queries always include that filter.

// Partial index: only index active users
db.users.createIndex(
  { email: 1 },
  { partialFilterExpression: { status: "active" } }
)

db.users.dropIndex("email_1")

Query Optimization

Slow queries usually mean a missing index, wrong compound index field order, or returning too many documents. Reduce payload size with projection and paginate with limit plus a sort on an indexed field.

Avoid blocking collection scans on large collections. If a query cannot use an index, redesign the query or add a compound index that covers the filter and sort together.

// Covered query: all fields in index
db.users.createIndex({ status: 1, name: 1, email: 1 })

db.users.find(
  { status: "active" },
  { projection: { _id: 0, name: 1, email: 1 } }
)

Using explain()

Run explain("executionStats") on find and aggregate operations to see whether an index was used, how many documents were examined, and execution time. totalDocsExamined should be close to nReturned for efficient queries.

Look for COLLSCAN stage in the winning plan—that indicates a collection scan. IXSCAN means an index scan. Compare executionStats before and after adding an index to confirm improvement.

executionStats.executionTimeMillis shows server-side execution time
nReturned vs totalDocsExamined ratio indicates index selectivity
Use aggregate $indexStats to find unused indexes over time

db.orders.find({ customerId: ObjectId("..."), status: "open" })
  .explain("executionStats")

Production Index Strategy

Maintain an index catalog tied to application query patterns. When adding features, add indexes in the same deployment or earlier, not after users report slowness.

Monitor index size relative to working set RAM. If indexes do not fit in memory, random disk reads increase latency dramatically. Archive cold data to separate collections or use tiered storage for historical records.