Fix MongoDB Index Not Used in Query

What's Actually Happening

MongoDB queries perform full collection scans despite existing indexes. Query performance is slow, and execution plan shows COLLSCAN instead of IXSCAN.

The Error You'll See

```bash $ db.collection.explain("executionStats").find({name: "test"})

winningPlan: { stage: "COLLSCAN" // Full collection scan } ```

Slow query log:

{
  "op": "query",
  "ns": "database.collection",
  "millis": 5000,
  "planSummary": "COLLSCAN"
}

Query taking long:

Query execution time: 10000ms
Documents scanned: 1000000

Index exists but not used:

```bash $ db.collection.getIndexes() [ {v: 2, key: {name: 1}, name: "name_1"} ]

// But explain shows COLLSCAN ```

Why This Happens

1. 1.Query shape mismatch - Query doesn't match index key pattern
2. 2.Index prefix not used - Query doesn't use index prefix
3. 3.No equality on first field - Range query on first indexed field
4. 4.Index order wrong - Query order doesn't match index order
5. 5.Negation or $nin - Operators that can't use indexes efficiently
6. 6.Index hidden - Index marked as hidden

Step 1: Check Query Execution Plan

```javascript // Basic explain: db.collection.explain().find({name: "test"})

// With execution stats: db.collection.explain("executionStats").find({name: "test"})

// All plans: db.collection.explain("allPlansExecution").find({name: "test"})

// Check winningPlan: // IXSCAN = index scan (good) // COLLSCAN = collection scan (bad)

// Key fields in explain: // - winningPlan.stage: IXSCAN or COLLSCAN // - winningPlan.indexName: index used // - executionStats.totalDocsExamined: documents scanned // - executionStats.totalKeysExamined: index keys scanned // - executionStats.nReturned: documents returned

// Check index usage: var explain = db.collection.explain("executionStats").find({name: "test"}) printjson(explain.executionStats)

// Check if index is winning: explain.queryPlanner.winningPlan.inputStage.indexName ```

Step 2: Check Available Indexes

```javascript // List all indexes: db.collection.getIndexes()

// Check index key pattern: db.collection.getIndexes().forEach(function(idx) { print(idx.name + ": " + JSON.stringify(idx.key)) })

// Check index size: db.collection.stats().indexSizes

// Check index build status: db.currentOp({ $or: [ { op: "command", "command.createIndexes": { $exists: true } }, { op: "none", ns: /system\.indexes/ } ] })

// Check if index is hidden: db.collection.getIndexes().forEach(function(idx) { if (idx.hidden) print("Hidden index: " + idx.name) }) ```

Step 3: Match Query to Index

```javascript // Index: {name: 1, age: 1} // Compound index - order matters

// Good - uses index: db.collection.find({name: "test"}) // First field equality db.collection.find({name: "test", age: 25}) // Both fields

// Bad - can't use index efficiently: db.collection.find({age: 25}) // Skips first field db.collection.find({name: {$regex: "test"}}) // Regex not efficient

// Index prefix rule: // Query must use prefix of index keys // {name: 1} is prefix of {name: 1, age: 1}

// Check index selection: db.collection.explain().find({age: 25}).queryPlanner.winningPlan

// Use hint to force index: db.collection.find({age: 25}).hint({age: 1}) ```

Step 4: Fix Query Operators

```javascript // Operators that can use indexes: // $eq, $lt, $lte, $gt, $gte, $in (with exact values) // $regex (with anchor ^)

// Operators that can't use indexes well: // $ne, $nin, $not, $or (except with specific conditions) // $exists (except with sparse indexes) // $regex (without anchor)

// Bad - $nin: db.collection.find({status: {$nin: ["active", "pending"]}}) // This does COLLSCAN

// Fix - use $in with inverted logic: db.collection.find({status: {$in: ["inactive", "deleted"]}})

// Bad - $ne: db.collection.find({status: {$ne: "active"}})

// Fix - use $in: db.collection.find({status: {$in: ["inactive", "deleted", "pending"]}})

// Bad - $or without indexes: db.collection.find({$or: [{name: "a"}, {age: 25}]}) // COLLSCAN if no index on both fields

// Fix - create indexes for each $or field: db.collection.createIndex({name: 1}) db.collection.createIndex({age: 1}) ```

Step 5: Fix Regex Queries

```javascript // Bad - regex without anchor: db.collection.find({name: {$regex: "test"}}) // COLLSCAN

// Good - regex with prefix anchor: db.collection.find({name: {$regex: "^test"}}) // Can use index

// Good - case-sensitive anchored regex: db.collection.find({name: {$regex: "^Test$", $options: ""}}) // Uses index

// Bad - case-insensitive: db.collection.find({name: {$regex: "test", $options: "i"}}) // COLLSCAN (can't use index)

// Fix - use collation index: db.collection.createIndex( {name: 1}, {collation: {locale: "en", strength: 2}} ) db.collection.find({name: "test"}).collation({locale: "en", strength: 2})

// Or use text search: db.collection.createIndex({name: "text"}) db.collection.find({$text: {$search: "test"}}) ```

Step 6: Check Index Order (ESR Rule)

```javascript // ESR Rule: Equality, Sort, Range // Index order should be: equality fields, sort fields, range fields

// Query: find({status: "active"}).sort({created: -1}).limit(10)

// Bad index: {created: 1, status: 1} // Sort field before equality - inefficient

// Good index: {status: 1, created: -1} // Equality first, then sort

// Query: find({status: "active", age: {$gt: 20}}).sort({name: 1})

// Good index: {status: 1, name: 1, age: 1} // Equality (status), Sort (name), Range (age)

// Check if sort uses index: db.collection.explain().find({status: "active"}).sort({created: -1})

// Look for SORT stage (bad - in-memory sort) // Or IXSCAN with direction (good - index sort) ```

Step 7: Create Correct Indexes

```javascript // Create single field index: db.collection.createIndex({name: 1})

// Create compound index: db.collection.createIndex({name: 1, age: 1})

// Create with options: db.collection.createIndex( {name: 1, age: 1}, {name: "name_age_idx", background: true} )

// Create unique index: db.collection.createIndex({email: 1}, {unique: true})

// Create sparse index (only indexed docs with field): db.collection.createIndex({nickname: 1}, {sparse: true})

// Create partial index (conditional): db.collection.createIndex( {status: 1}, {partialFilterExpression: {status: {$in: ["active", "pending"]}}} )

// Create TTL index: db.collection.createIndex({createdAt: 1}, {expireAfterSeconds: 3600}) ```

Step 8: Use Index Hint

```javascript // Force specific index: db.collection.find({name: "test"}).hint({name: 1})

// Force by index name: db.collection.find({name: "test"}).hint("name_1")

// Check if hint works: db.collection.explain().find({name: "test"}).hint({name: 1})

// Disable all indexes (use natural order): db.collection.find({name: "test"}).hint({$natural: 1})

// Note: Use hint for testing, not production // Better to fix query or create proper index ```

Step 9: Analyze Query Pattern

```javascript // Use $indexStats: db.collection.aggregate([{$indexStats: {}}])

// Shows: // - accesses: number of times index used // - usageCount: query count

// Check slow operations: db.system.profile.find({ millis: {$gt: 100}, planSummary: "COLLSCAN" }).sort({ts: -1}).limit(10)

// Enable profiler: db.setProfilingLevel(1, 100) // Log queries > 100ms

// Check current operations: db.currentOp({ "query.planSummary": "COLLSCAN", secs_running: {$gt: 5} })

// Use MongoDB Compass for visual analysis ```

Step 10: MongoDB Index Verification Script

```javascript // Create verification script: // Save as check-index-usage.js

var collectionName = "collection"; var dbName = "database";

var db = db.getSiblingDB(dbName); var coll = db.getCollection(collectionName);

print("=== Collection Stats ==="); printjson(coll.stats());

print("\n=== Indexes ==="); coll.getIndexes().forEach(function(idx) { print(idx.name + ": " + JSON.stringify(idx.key) + (idx.hidden ? " (HIDDEN)" : "") + (idx.unique ? " (UNIQUE)" : "")); });

print("\n=== Index Usage Stats ==="); coll.aggregate([{$indexStats: {}}]).forEach(function(stat) { print(stat.name + ": " + stat.accesses.ops + " operations"); });

print("\n=== Recent Slow Queries with COLLSCAN ==="); db.system.profile.find({ ns: dbName + "." + collectionName, planSummary: "COLLSCAN" }).sort({ts: -1}).limit(5).forEach(function(op) { print("Query: " + JSON.stringify(op.query)); print("Time: " + op.millis + "ms"); print("---"); });

print("\n=== Index Sizes ==="); printjson(coll.stats().indexSizes);

// Run: mongo database check-index-usage.js ```

MongoDB Index Usage Checklist

Verify the Fix

```javascript # After fixing index usage

# 1. Check execution plan db.collection.explain("executionStats").find({name: "test"}) // winningPlan.stage: IXSCAN

# 2. Check scanned vs returned // totalKeysExamined ≈ nReturned

# 3. Compare before/after // Query time reduced significantly

# 4. Monitor index stats db.collection.aggregate([{$indexStats: {}}]) // Accesses increasing

# 5. Check profile db.system.profile.find({millis: {$gt: 100}}) // No COLLSCAN for this query

# 6. Test with hint db.collection.find({name: "test"}).hint({name: 1}) // Same performance ```

Related Issues

• [Fix MongoDB Query Timeout](/articles/fix-mongodb-query-timeout)
• [Fix MongoDB Index Build Failed](/articles/fix-mongodb-index-build-failed)
• [Fix MongoDB Slow Query](/articles/fix-mongodb-slow-query)