MongoDB Indexes — Types, Compound Indexes, Covered Queries & explain() Analysis

Without an index, every MongoDB query scans every document in the collection. On 10 documents, nobody notices. On 10 million, your API times out. Indexes are the difference between a COLLSCAN (full scan) and an IXSCAN (index scan) — and they’re the single biggest lever for MongoDB query performance.

This post builds on the Spring Boot + MongoDB post and focuses entirely on indexes — types, compound index design, covered queries, and reading explain() output.

COLLSCAN vs IXSCAN

Run a query without an index:

db.orders.find({ customerId: "cust-123" }).explain("executionStats")

{
  "winningPlan": {
    "stage": "COLLSCAN",
    "filter": { "customerId": { "$eq": "cust-123" } }
  },
  "executionStats": {
    "totalDocsExamined": 500000,
    "totalKeysExamined": 0,
    "executionTimeMillis": 340
  }
}

500K documents examined to find maybe 50 results. Now add an index:

db.orders.createIndex({ customerId: 1 })

Same query:

{
  "winningPlan": {
    "stage": "IXSCAN",
    "indexName": "customerId_1"
  },
  "executionStats": {
    "totalDocsExamined": 50,
    "totalKeysExamined": 50,
    "executionTimeMillis": 1
  }
}

340ms to 1ms. The index lets MongoDB jump directly to the matching documents.

Index types

Single field

@Document(collection = "orders")
@CompoundIndex(def = "{}") // placeholder — see compound section below
data class Order(
    @Id val id: String? = null,
    @Indexed val customerId: String,
    @Indexed val status: OrderStatus,
    val total: BigDecimal,
    val createdAt: Instant = Instant.now()
)

@Indexed creates a single-field index. Good for queries that filter on one field.

Compound indexes and the ESR rule

When queries filter and sort on multiple fields, you need a compound index. The ESR rule (Equality, Sort, Range) determines field order:

1. Equality fields first — exact matches (status = "SHIPPED")
2. Sort fields next — fields in your sort() clause
3. Range fields last — range conditions (total > 100, date ranges)

Example query: find shipped orders over $100, sorted by date:

db.orders.find({
  status: "SHIPPED",
  total: { $gt: 100 }
}).sort({ createdAt: -1 })

Following ESR, the optimal index is:

db.orders.createIndex({ status: 1, createdAt: -1, total: 1 })
// Equality: status | Sort: createdAt | Range: total

In Spring Data:

@Document(collection = "orders")
@CompoundIndex(name = "status_date_total", def = "{ 'status': 1, 'createdAt': -1, 'total': 1 }")
data class Order(
    @Id val id: String? = null,
    val customerId: String,
    val status: OrderStatus,
    val total: BigDecimal,
    val createdAt: Instant = Instant.now()
)

Why ESR? Equality narrows to exact matches first (most selective). Sort order in the index matches the query sort, so MongoDB doesn’t need an in-memory sort. Range comes last because it produces a range of index keys — putting range before sort forces an in-memory sort.

Text indexes

@Document(collection = "products")
@CompoundIndex(name = "text_search", def = "{ 'name': 'text', 'description': 'text' }")
data class Product(
    @Id val id: String? = null,
    val name: String,
    val description: String,
    val category: String
)

db.products.find({ $text: { $search: "wireless keyboard" } })

Text indexes support full-text search but are limited compared to dedicated search engines. For complex search requirements, consider Elasticsearch or Atlas Search.

TTL indexes

Automatically delete documents after a time period:

@Indexed(expireAfter = "30d")
val createdAt: Instant = Instant.now()

Useful for session data, temporary tokens, or audit logs that should expire.

Covered queries

A query is “covered” when the index contains all fields needed — both for filtering and for the response. MongoDB doesn’t need to fetch the actual document at all.

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

// Covered — all returned fields are in the index
db.orders.find(
  { customerId: "cust-123" },
  { status: 1, total: 1, _id: 0 }  // projection excludes _id
).explain("executionStats")

{
  "executionStats": {
    "totalDocsExamined": 0,
    "totalKeysExamined": 50
  }
}

totalDocsExamined: 0 — MongoDB answered the query entirely from the index. This is as fast as it gets.

Key requirements for covered queries:

• All query fields are in the index
• All returned fields are in the index
• _id is excluded from the projection (unless _id is in the index)

Reading explain() output

The key metrics

Metric	What it means	What to look for
totalDocsExamined	Documents fetched from disk	Should be close to results returned
totalKeysExamined	Index entries scanned	Should be close to docs examined
executionTimeMillis	Total query time	Lower is better
stage	Query execution strategy	IXSCAN good, COLLSCAN bad
nReturned	Documents returned	Compare to docsExamined

Red flags

• COLLSCAN — no usable index. Add one.
• totalDocsExamined >> nReturned — index exists but isn’t selective enough. Check if a compound index would narrow results.
• SORT stage after IXSCAN — in-memory sort. Your index doesn’t cover the sort order. Reorder index fields following ESR.
• totalKeysExamined >> totalDocsExamined — scanning too many index entries. Your index prefix doesn’t match the query well.

Using explain() in Spring Boot

import org.springframework.data.mongodb.core.MongoTemplate
import org.springframework.data.mongodb.core.query.Criteria
import org.springframework.data.mongodb.core.query.Query
import org.bson.Document

fun explainQuery(mongoTemplate: MongoTemplate) {
    val query = Query(Criteria.where("customerId").`is`("cust-123"))
    val collection = mongoTemplate.getCollection("orders")
    val explanation: Document = collection
        .find(query.queryObject)
        .explain()
    println(explanation.toJson())
}

Run explain() on your critical queries before deploying to production. If you see COLLSCAN or high totalDocsExamined, add or adjust indexes.

Index selection and hint()

MongoDB’s query planner usually picks the right index. When it doesn’t — often with multiple candidate indexes — you can force it:

db.orders.find({ customerId: "cust-123", status: "SHIPPED" })
  .hint({ customerId: 1, status: 1, createdAt: -1 })

In Spring Data:

val query = Query(
    Criteria.where("customerId").`is`("cust-123")
        .and("status").`is`("SHIPPED")
).withHint("{ 'customerId': 1, 'status': 1, 'createdAt': -1 }")

Use hint() sparingly — it overrides the query planner permanently. If your data distribution changes, the hint may become suboptimal.

Managing indexes in Spring Boot

Annotation-based

@Document(collection = "orders")
@CompoundIndex(name = "customer_status", def = "{ 'customerId': 1, 'status': 1 }")
@CompoundIndex(name = "status_date_total", def = "{ 'status': 1, 'createdAt': -1, 'total': 1 }")
data class Order(
    @Id val id: String? = null,
    @Indexed val customerId: String,
    val status: OrderStatus,
    val total: BigDecimal,
    val createdAt: Instant = Instant.now()
)

Requires spring.data.mongodb.auto-index-creation=true in application.yml.

Programmatic with MongoTemplate

For more control — especially in production where you want to create indexes in the background:

import org.springframework.boot.context.event.ApplicationReadyEvent
import org.springframework.context.event.EventListener
import org.springframework.data.domain.Sort
import org.springframework.data.mongodb.core.MongoTemplate
import org.springframework.data.mongodb.core.index.Index
import org.springframework.data.mongodb.core.index.IndexOperations
import org.springframework.stereotype.Component

@Component
class IndexInitializer(
    private val mongoTemplate: MongoTemplate
) {

    @EventListener(ApplicationReadyEvent::class)
    fun createIndexes() {
        val ops: IndexOperations = mongoTemplate.indexOps("orders")

        ops.ensureIndex(
            Index()
                .on("customerId", Sort.Direction.ASC)
                .on("status", Sort.Direction.ASC)
                .named("customer_status")
                .background()
        )

        ops.ensureIndex(
            Index()
                .on("status", Sort.Direction.ASC)
                .on("createdAt", Sort.Direction.DESC)
                .on("total", Sort.Direction.ASC)
                .named("status_date_total")
                .background()
        )
    }
}

.background() creates the index without blocking reads and writes — critical for production deployments.

Common mistakes

• No index on filter/sort fields — the most common performance problem. Every field you filter or sort by in production queries needs an index.
• Too many indexes — each index slows down writes and uses memory. Index what you query, drop what you don’t. Check db.collection.getIndexes() regularly.
• Ignoring the ESR rule — wrong field order in compound indexes forces in-memory sorts or scans more keys than necessary.
• No explain() before production — always verify your critical queries use the expected index. A missing index on a high-traffic endpoint turns into a production incident.
• Creating indexes during peak traffic — foreground index creation locks the collection. Always use .background() or schedule during low-traffic windows.
• Duplicate indexes — a compound index { a: 1, b: 1 } covers queries on a alone. You don’t need a separate { a: 1 } index.