Query Optimization Tasks

Introduction

Query optimization is mostly about making slow things fast by changing the index, the query shape, or both. These tasks each start from a slow query and walk you through the diagnosis with explain() and the fix.

Understanding the topic

The diagnosis checklist for every slow query:

• winningPlan.stage — IXSCAN good, COLLSCAN bad.
• SORT stage present? An index can usually remove it.
• totalDocsExamined vs. nReturned — should be close to 1:1.
• executionTimeMillis across runs — was the cache cold?
• indexBounds — are they tight or open-ended?

Informative example

Task — slow "recent orders by user" goes from COLLSCAN to 1 ms:

// SLOW (COLLSCAN)
db.orders.find({ userId, status:"paid" }).sort({ createdAt:-1 }).limit(20)
         .explain("executionStats");
// → totalDocsExamined: 5 000 000, executionTimeMillis: 1 800

// FIX
db.orders.createIndex({ userId:1, status:1, createdAt:-1 });

// AFTER (IXSCAN + no SORT stage)
db.orders.find({ userId, status:"paid" }).sort({ createdAt:-1 }).limit(20)
         .explain("executionStats");
// → totalDocsExamined: 20, executionTimeMillis: 1

Best practices

• Always read executionStats, not just winningPlan.
• One compound index can replace many single-field indexes — consolidate.
• Use Atlas Performance Advisor for index suggestions backed by real traffic.