Practice & Assessment
Test your understanding of Indexing and Query Performance
Multiple Choice Questions
5According to the ESR rule, what is the correct field order for a compound index on a query that filters by userId (equality), sorts by createdAt, and filters by amount > 100 (range)?
You see totalDocsExamined: 500000 and nReturned: 3 in an explain() output. What does this indicate?
What is the maximum number of text indexes allowed per MongoDB collection?
A TTL index is set with expireAfterSeconds: 3600 on the createdAt field. A document's createdAt is 2 hours ago. When will MongoDB delete it?
What does a SORT stage appearing above an IXSCAN in an explain() winning plan indicate?
Coding Challenges
1Index an E-commerce Orders Collection
You have an 'orders' collection with fields: userId (string), status (string: pending/shipped/delivered), total (number), createdAt (Date), items (array of objects). Write a Node.js script that: (1) Creates a compound index following ESR for the query: find orders by userId, sort by createdAt descending, filter total > 100. (2) Creates a TTL index that expires orders in 'pending' status after 24 hours using a partial filter index. (3) Runs explain('executionStats') on a sample query and prints totalDocsExamined and nReturned. Input: connect to a local MongoDB with seeded data (provide a seed script too). Output: index confirmation and explain stats. Estimated time: 30 minutes.
Mini Project
Query Performance Profiler Tool
Build a Node.js script that acts as a mini query profiler. It connects to MongoDB, enables the slow query profiler (db.setProfilingLevel(1, { slowms: 100 })), runs 5 different queries of increasing complexity against a seeded 'products' collection (including one COLLSCAN and one IXSCAN), reads the system.profile collection to extract executionTimeMillis and plan stages for each query, and outputs a formatted comparison table to the console. The script must also create an optimal index for the slowest query and show the before/after execution time. Concepts used: index creation, explain(), profiler collection, ESR rule, IXSCAN vs COLLSCAN.