10. Query Profiling, explain("executionStats") & Index Covering Analysis • MongoDB & NoSQL Architecture: CRUD to Sharding & WiredTiger • Rishabh Ki Kaksha

MongoDB & NoSQL Architecture: CRUD to Sharding & WiredTiger › 10. Query Profiling, explain("executionStats") & Index Covering Analysis

45 mins

← Prev Next →

Course Syllabus

Module 1: MongoDB Foundations, BSON & CRUD Mechanics

01. NoSQL vs SQL: CAP Theorem & Document-Oriented Architecture 02. BSON Data Types & Document Schema Design (Embedding vs Referencing) 03. High-Performance CRUD Operations, BulkWrite & Write Concerns 04. Schema Validation with JSON Schema ($jsonSchema)

Module 2: Advanced Aggregation Framework & Data Pipelines

05. Aggregation Pipeline Core: $match, $project, $group, $sort & $limit 06. Complex Transformations: $unwind, $lookup (Joins) & $facet (Multi-Facet Analytics) 07. Window Operators ($setWindowFields) & Accumulator Functions

Module 3: Indexing Strategies & Query Optimization

08. B-Tree Indexes in MongoDB: Single Field, Compound & Partial Indexes 09. Special Indexes: TTL (Time-To-Live), Geospatial (2dsphere) & Text Indexes 10. Query Profiling, explain("executionStats") & Index Covering Analysis

Module 4: Storage Engine Internals (WiredTiger) & ACID Transactions

11. WiredTiger Storage Engine Architecture: Cache, Compression & Checkpoints 12. Read Concerns (local, majority, linearizable) & Write Concerns (w:majority, j:true) 13. Multi-Document Distributed ACID Transactions with Sessions

Module 5: High Availability, Replica Sets, Sharding & Backups

14. Replica Sets Architecture: Primary Election, Oplog & Automatic Failover 15. Sharding Architecture: mongos Router, Config Servers & Shard Key Selection 16. Range vs Hash Sharding, Chunk Balancing & Jumbo Chunks Mitigation 17. Change Streams: Building Real-Time Event-Driven Pipelines with MongoDB 18. Production Backup Strategies: mongodump/mongorestore, Point-in-Time & Atlas CLI

Interactive Lesson Studio

# 10. Query Profiling, explain("executionStats") & Index Covering Analysis

> MongoDB Notes by Rishabh - Topic 10

---

### 1. Database Query Profiler 🕵️

Production me kaunsi queries slow chal rahi hain unhe track karna:

```javascript
// Profile queries taking longer than 100ms:
db.setProfilingLevel(1, { slowms: 100 });

// View top 5 slowest queries from system.profile:
db.system.profile.find().sort({ millis: -1 }).limit(5);
```

---

### 2. Reading `explain("executionStats")` Like a Senior DBA 🔬

```javascript
db.users.find({ email: "rishabh@boost.in" }).explain("executionStats");
```

#### Key Metrics to Inspect:
1. **`stage`:**
   * `COLLSCAN` ➔ ❌ **Disaster (Full Collection Scan - No index used!)**
   * `IXSCAN` ➔ ✅ **Great (Index Scan used!)**
   * `FETCH` ➔ Fetching full document from disk.
2. **`totalDocsExamined` vs `nReturned`:**
   * Agar `totalDocsExamined: 1000000` aur `nReturned: 1` ➔ **Missing Index!**
   * Ideal: `totalDocsExamined === nReturned`.

---

### 3. Covered Queries (The Golden Nirvana of Databases 👑)

Covered Query ka matlab: **MongoDB ko disk par document kholne ki zaroorat hi nahi padi (`totalDocsExamined: 0`) — saara data 100% RAM ke andar B-Tree Index se hi mil gaya!**

```javascript
// Index:
db.users.createIndex({ username: 1, role: 1 });

// Covered Query (Only request indexed fields, suppress _id):
db.users.find(
    { username: "rishabh" },
    { username: 1, role: 1, _id: 0 } // _id: 0 is mandatory!
).explain("executionStats");

// executionStats:
// stage: "PROJECTION_COVERED"
// totalDocsExamined: 0  <=== ZERO DISK SEEKS! ⚡
```

---

### 4. Master Comparison Matrix

---

### 5. Interview Ke 3 Sawal Is Topic Se

1. **Covered query me `_id: 0` specify karna kyu mandatory hota hai?** -> Kyunki MongoDB default me `_id` field return karta hai; agar `_id` compound index ka hissa na ho, toh database ko `_id` laane ke liye disk par document fetch karna padega jisse query covered nahi rahegi.
2. **`executionStats` me `totalKeysExamined` aur `totalDocsExamined` me kya fark hai?** -> `totalKeysExamined` index me scan kiye gaye B-tree pointers ki count hai; `totalDocsExamined` disk par actual read kiye gaye full documents ki count hai.
3. **Database profiling production me permanent Level 2 (all operations) par kyu nahi rakhni chahiye?** -> Kyunki har query ko `system.profile` capped collection me write karne se 10-15% I/O throughput degrade ho jata hai.

---
**Next Topic:** 11. WiredTiger Storage Engine Architecture: Cache, Compression & Checkpoints

Loading lesson content...

Python 3.12

Full IDE

Live Execution Terminal

Click "Execute Live" to run code.
                

Lesson Mastery AI Quiz

AI Doubt Solver

CERTIFICATE OF COMPLETION

Rishabh Gehlot

PHP 8+ & Laravel 11 Master Track