Designing indexes for real queries: a step-by-step guide

Introduction: Why index design matters for production systems

Most teams don't think about indexes until queries start timing out. By then, you're adding them reactively—and often in the wrong order. Designing indexes for real queries: a step-by-step guide flips this around. Instead of guessing, we work backward from actual application behavior, systematically translate screen requirements into query patterns, and build indexes that serve multiple queries efficiently.

In this article, we'll walk through a practical approach that senior engineers use across production systems. We'll start with a real e-commerce scenario, map out its queries, apply the ESR rule, and verify everything with explain() so you know exactly what your database is doing.

Step 1: List queries — Write down filters and sorts for each screen

Before you create a single index, you need to understand what queries actually run. This means going through your application screen by screen, not guessing based on schema design.

Let's work with a concrete example: an e-commerce platform with an orders collection. Here's what we'd document:

• Orders dashboard (admin): Find all orders with status "pending", sorted by creation date (newest first)
• User order history: Find orders by user_id, filtered by status ("shipped" or "delivered"), sorted by date descending
• Fulfillment queue: Find orders where status is "pending" and warehouse_id matches, sorted by priority descending, then date ascending
• Search page: Find orders where customer_email contains a substring, optionally filtered by date range and status
• Analytics export: Aggregate orders by status and date range

Write these down. Every screen. Every filter. Every sort. Here's what this documentation might look like for the main queries:

// Orders dashboard
db.orders.find({
  status: "pending"
}).sort({ created_at: -1 }).limit(50)

// User order history
db.orders.find({
  user_id: ObjectId("..."),
  status: { $in: ["shipped", "delivered"] }
}).sort({ created_at: -1 }).limit(20)

// Fulfillment queue
db.orders.find({
  status: "pending",
  warehouse_id: ObjectId("...")
}).sort({ priority: -1, created_at: 1 }).limit(100)

// Analytics (monthly revenue)
db.orders.aggregate([
  { $match: { created_at: { $gte: start, $lt: end } } },
  { $group: { _id: "$status", total: { $sum: "$amount" } } }
])

This step forces you to be honest about what your system actually needs. You might discover some queries don't exist yet, or that you're overthinking edge cases.

Step 2: Group them — One compound index can serve several queries

Now look for patterns. Often multiple queries can share a single compound index. This is where efficiency comes from—not creating 10 single-field indexes, but finding the 3–4 compound indexes that cover 90% of your workload.

Let's group our examples:

Notice that the analytics query and the dashboard query both touch status and created_at. One index might serve both, depending on the specifics.

The key principle: a single compound index can serve multiple queries if their filter and sort fields appear in the same order at the beginning of the index. If one query needs {status, created_at} and another needs {status, priority}, you can't share an index—but if both need {status, created_at}, a single index handles both.

Step 3: Apply ESR — Equality first, then sort, then range

ESR (Equality, Sort, Range) is the golden rule for index field ordering. It tells you exactly how to arrange your compound index fields for maximum efficiency.

• Equality fields first: All fields used with exact matches (==, $in with small lists)
• Sort fields next: Fields used in the sort clause
• Range fields last: Fields used with inequality operators (>, <, $in with large lists, regex)

Let's apply ESR to our fulfillment queue query:

// Query
db.orders.find({
  status: "pending",              // equality
  warehouse_id: ObjectId("..."),  // equality
  created_at: { $gte: someDate }  // range (not in this example, but conceptually)
}).sort({ priority: -1, created_at: 1 })

// ESR index order:
// 1. Equality: status, warehouse_id
// 2. Sort: priority, created_at
// 3. Range: (none in this query)
// Result:
db.orders.createIndex({
  status: 1,
  warehouse_id: 1,
  priority: -1,
  created_at: 1
})

Why does order matter? MongoDB reads the index left to right. It first filters to all documents with status="pending", then within those it narrows to warehouse_id, then it has the data already sorted by priority and created_at. Without ESR, MongoDB either can't use the index at all, or uses it partially and then does an in-memory sort (much slower).

For the user order history query, ESR gives us:

// Query
db.orders.find({
  user_id: ObjectId("..."),       // equality
  status: { $in: [...] }          // equality
}).sort({ created_at: -1 })       // sort

// ESR index:
db.orders.createIndex({
  user_id: 1,
  status: 1,
  created_at: -1
})

One subtlety: when you have multiple equality fields, their order doesn't matter much for filtering performance. But when combined with a sort, they should come before the sort field. And sort direction (ascending vs. descending) only matters if you have multiple sort fields—MongoDB can reverse-scan an index, so a {created_at: 1} index works for both ascending and descending sorts.

Step 4: Verify — explain() every important query

Theory is good. Production is better. Use explain() to verify that MongoDB actually uses your index the way you think it does.

db.orders.find({
  status: "pending",
  warehouse_id: ObjectId("...")
}).sort({ priority: -1, created_at: 1 }).explain("executionStats")

Look for these fields in the output:

• stage: "COLLSCAN" — Bad. Full collection scan. Your index isn't being used.
• stage: "IXSCAN" — Good. Index scan. Documents are being selected via index.
• stage: "SORT" with "executionStages.stage" — Careful. If SORT appears in the execution plan, MongoDB is sorting in memory, not using the index for sorting. Only concerning if you're sorting millions of documents.
• executionStats.totalDocsExamined vs. executionStats.nReturned — These should be close. If you examine 10,000 docs but return 50, your index isn't filtering efficiently.
• executionStats.executionStages.stage — The actual stage that executed. IXSCAN with a FETCH is normal (you still need to fetch document fields not in the index).

Here's a realistic output snippet:

{
  "executionStats": {
    "executionStages": {
      "stage": "SORT",
      "nReturned": 50,
      "executionStages": {
        "stage": "FETCH",
        "totalDocsExamined": 500,
        "executionStages": {
          "stage": "IXSCAN",
          "keyPattern": { status: 1, warehouse_id: 1, priority: -1, created_at: 1 },
          "keysExamined": 500
        }
      }
    }
  }
}

This is acceptable: we scanned 500 index keys, fetched 500 documents, and returned the top 50. The index is doing its job. If nReturned were 50 but totalDocsExamined were 50,000, something's wrong with the index or query.

Common mistakes

Creating too many indexes. Each index consumes disk space, slows writes, and makes query optimization harder. Start with 3–5 compound indexes that cover your critical paths. Add more only when explain() shows a real problem.

Ignoring range queries. Putting a range field (e.g., created_at with $gte) before a sort field breaks index efficiency. ESR prevents this, but it's easy to violate if you're building indexes ad-hoc.

Assuming single-field indexes are safe.** An index on {user_id} by itself doesn't guarantee sort efficiency. {user_id, created_at} is what you probably need. Always think compound.

Not accounting for write performance. Every index slows down inserts and updates. In high-volume systems, 10 indexes can double your write latency. Design indexes for reads, but monitor write impact.

Forgetting sparse and partial indexes. If only 5% of documents have a particular field, a sparse index ({field: 1, sparse: true}) is smaller and faster. For status-based queries where only "pending" orders matter, a partial index ({status: 1, created_at: 1}, {partialFilterExpression: {status: "pending"}}) saves space.

Key takeaways

• List every query and its filters/sorts explicitly. Don't guess.
• Group queries to identify shared index patterns and reduce total index count.
• Apply ESR rigorously: equality fields, sort fields, range fields—in that order.
• Verify with explain() on representative data volumes. totalDocsExamined and nReturned should be close.
• Start conservative: 3–5 compound indexes cover most workloads. Add more only when you have evidence.
• Consider sparse and partial indexes to reduce disk footprint and write overhead.
• Revisit indexes quarterly as workload patterns change. A query that was infrequent might become critical.