Identifying missing indexes in MongoDB - Mini Project: Build & Apply

Identifying Missing Indexes in MongoDB

📖 Scenario: You are managing a MongoDB database for an online bookstore. The database has a collection called books that stores information about each book, including its title, author, and genre. To improve query performance, you want to identify which indexes are missing based on the queries run on the collection.

🎯 Goal: Build a MongoDB query to find missing indexes by analyzing the system.profile collection. You will create a query to find slow queries that could benefit from indexes, then configure a threshold for slow queries, and finally write a query to list those queries missing indexes.

📋 What You'll Learn

Create a query to find slow queries in the system.profile collection

Set a threshold variable for slow query duration in milliseconds

Write a query to find queries that took longer than the threshold and did not use an index

Add a final query to list the fields used in those slow queries to help identify missing indexes

💡 Why This Matters

🌍 Real World

Database administrators often need to find missing indexes to improve query speed and reduce server load.

💼 Career

Knowing how to analyze query performance and identify missing indexes is a key skill for database developers and administrators.

1

Create a query to find slow queries in system.profile

Write a MongoDB query called slowQueries that finds all documents in the system.profile collection where the millis field is greater than 100. Use the db.system.profile.find() method with a filter object.

MongoDB

// Your code here

Need a hint?

Use db.system.profile.find() with a filter object that checks if millis is greater than 100.

2

Set a threshold variable for slow query duration

Create a variable called slowThreshold and set it to 100 to represent the slow query duration threshold in milliseconds.

MongoDB

const slowQueries = db.system.profile.find({ millis: { $gt: 100 } });
// Your code here

Need a hint?

Declare a constant variable named slowThreshold and assign it the value 100.

3

Find queries slower than threshold without index usage

Write a MongoDB query called missingIndexQueries that finds documents in system.profile where millis is greater than slowThreshold and planSummary equals "COLLSCAN" (collection scan, meaning no index used). Use db.system.profile.find() with a filter object using slowThreshold.

MongoDB

const slowQueries = db.system.profile.find({ millis: { $gt: 100 } });
const slowThreshold = 100;
// Your code here

Need a hint?

Use db.system.profile.find() with a filter that checks millis > slowThreshold and planSummary equal to "COLLSCAN".

4

List fields used in slow queries missing indexes

Create a MongoDB aggregation called fieldsUsed on missingIndexQueries that groups by the command.filter field to identify which fields are used in slow queries without indexes. Use aggregate() with $group stage grouping by "$command.filter" and count the occurrences with $sum.

MongoDB

const slowQueries = db.system.profile.find({ millis: { $gt: 100 } });
const slowThreshold = 100;
const missingIndexQueries = db.system.profile.find({ millis: { $gt: slowThreshold }, planSummary: "COLLSCAN" });
// Your code here

Need a hint?

Use db.system.profile.aggregate() with $match to filter and $group to count by "$command.filter".