Identifying missing indexes in MongoDB - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Find queries missing indexes from system.profile

Given the system.profile collection in MongoDB that logs slow queries, which query will return all queries that did NOT use an index?

MongoDB

db.system.profile.find({ 'planSummary': 'COLLSCAN' })

A db.system.profile.find({ 'planSummary': 'COLLSCAN' })

B db.system.profile.find({ 'planSummary': { $ne: 'COLLSCAN' } })

C db.system.profile.find({ 'planSummary': 'IXSCAN' })

D db.system.profile.find({ 'planSummary': { $exists: false } })

💡 Hint

Look for queries that performed a collection scan instead of using an index.

Queries that do not use an index show 'COLLSCAN' in the planSummary field. Filtering for this value returns queries missing indexes.

🧠 Conceptual

intermediate

Why missing indexes cause slow queries

Why do missing indexes cause queries to be slow in MongoDB?

A Because missing indexes cause MongoDB to create temporary indexes automatically.

B Because MongoDB caches all queries without indexes.

C Because missing indexes cause MongoDB to lock the entire database.

D Because MongoDB must scan every document in the collection to find matches.

💡 Hint

Think about how MongoDB finds data without an index.

Without an index, MongoDB performs a collection scan, checking every document, which is slower than using an index.

📝 Syntax

advanced

Identify the correct aggregation to find missing indexes

Which aggregation pipeline correctly counts how many queries used collection scans (missing indexes) grouped by namespace?

MongoDB

db.system.profile.aggregate([ { $match: { planSummary: 'COLLSCAN' } }, { $group: { _id: '$ns', count: { $sum: 1 } } } ])

A db.system.profile.aggregate([ { $match: { planSummary: { $ne: 'COLLSCAN' } } }, { $group: { _id: '$ns', count: { $sum: 1 } } } ])

B db.system.profile.aggregate([ { $match: { planSummary: 'COLLSCAN' } }, { $group: { _id: '$ns', count: { $sum: 1 } } } ])

C db.system.profile.aggregate([ { $group: { _id: '$ns', count: { $sum: 1 } } }, { $match: { planSummary: 'COLLSCAN' } } ])

D db.system.profile.aggregate([ { $match: { planSummary: 'IXSCAN' } }, { $group: { _id: '$ns', count: { $sum: 1 } } } ])

💡 Hint

Match first, then group to count per namespace.

The pipeline first filters queries with 'COLLSCAN' then groups by namespace to count them. Changing order or condition breaks logic.

🔧 Debug

advanced

Why does this query not find missing indexes?

This query returns zero results but there are slow queries without indexes. Why? db.system.profile.find({ planSummary: { $eq: 'COLLSCAN' } })

A Because the system.profile collection is not enabled or profiling level is too low.

B Because the planSummary field sometimes contains 'COLLSCAN' inside an array, so $eq fails.

C Because the field name is case sensitive and should be 'planSummary' exactly.

D Because $eq operator is not supported in MongoDB queries.

💡 Hint

Check if profiling is enabled to log slow queries.

If profiling is off or set to a level that does not log slow queries, system.profile will be empty, so no results are found.

❓ optimization

expert

Optimize index usage for frequent queries

You have many slow queries scanning the orders collection filtering by customerId and sorting by orderDate. Which index will best improve performance?

A { customerId: 1 }

B { orderDate: -1, customerId: 1 }

C { customerId: 1, orderDate: -1 }

D { orderDate: 1 }

💡 Hint

Index fields should match the query filter first, then the sort order.

An index starting with customerId supports filtering, then orderDate supports sorting efficiently. The order of fields matters.