Level 1 · 20 min

CRUD & Indexes

MongoDB indexes are the primary tool for query performance. Understanding index types, the ESR rule for compound index design, and how to verify index usage with explain() is essential for production MongoDB operations.

Index Types

Single-field indexes index one field in ascending (1) or descending (-1) order. Compound indexes index multiple fields — the order matters for query coverage and sort support. Partial indexes index only documents matching a filter expression — e.g., index only active users status: active. This reduces index size and write overhead for sparse data. TTL indexes (expireAfterSeconds) automatically delete documents after a time period — used for session data, cache collections, and log expiration. Text indexes support $text full-text search (limited compared to Atlas Search). Multikey indexes automatically handle array fields — MongoDB creates one index entry per array element. You cannot create a compound index on two array fields.

ESR Rule for Compound Indexes

The ESR rule (Equality-Sort-Range) defines the optimal field order in a compound index. Equality fields first: fields used with exact match operators ($eq, $in with few values) should appear earliest in the index — they filter out the most documents fastest. Sort fields second: fields used in sort (orderBy) appear next — the index can satisfy the sort without an in-memory sort stage. Range fields last: fields used with $gt, $lt, $gte, $lte appear at the end — they are applied after equality and sort have filtered the candidate set. Example: query filters status='active', sorts by createdAt DESC, ranges on price: index is (status, createdAt, price). Key insight from MongoDB: The Definitive Guide (3rd ed.): selectivity is the central objective when designing indexes. The book demonstrates this with a concrete example — a multivalue query on student_id > 500,000 against a 1M-document collection had poor selectivity because it matched roughly half the records, making the index 'worse than a collection scan' when combined with a low-selectivity range. Starting with 4.2, MongoDB replaced foreground and background index builds with a hybrid build that holds an exclusive lock only at the start and end of the build, yielding to read/write operations during the bulk phase — eliminating the production impact of large index creation.

Index Selectivity and Covering Queries

Selectivity measures how many documents an index key eliminates. High selectivity: an email field where each value is unique — the index returns one document per query. Low selectivity: a boolean field where half the documents have each value — the index scans 50% of documents. Low-selectivity indexes on their own are often worse than a full collection scan because they add index overhead. A covering query returns all required data from the index alone — no document fetch needed. This requires all projected fields to be in the index. Example: index on (status, email) and query projects only status and email — MongoDB never reads the document, only the index.

Key Takeaways

ESR rule: Equality first, Sort second, Range last. This ordering maximizes index utilization across all three query phases.
Partial indexes are under-used: indexing only the relevant subset (e.g., active users) reduces index size and write overhead without sacrificing query performance.
Covering queries avoid document fetches entirely — the fastest MongoDB queries are those served entirely from the index.

Code example

// ESR compound index: status (E), createdAt (S), price (R)\ndb.orders.createIndex(\n  {status: 1, createdAt: -1, price: 1},\n  {name: 'idx_orders_esr}\n)\n\n// Partial index: only active orders\ndb.orders.createIndex(\n  {customerId: 1},\n  {partialFilterExpression: {status: 'active}}'\n)\n\n// TTL index: auto-delete sessions after 1 hour\ndb.sessions.createIndex(\n  {createdAt: 1},\n  {expireAfterSeconds: 3600}'\n)