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.

Practice

1. What is the main reason to create indexes in MongoDB collections?

easy

A. To store more data in the database

B. To make queries run faster by helping MongoDB find data quickly

C. To backup the database automatically

D. To encrypt the data for security

Solution

1. Step 1: Understand the purpose of indexes

   Indexes are special data structures that help MongoDB quickly locate data without scanning the entire collection.

2. Step 2: Compare options with index purpose

   Only To make queries run faster by helping MongoDB find data quickly correctly states that indexes speed up queries by helping find data faster.

3. Final Answer:

   To make queries run faster by helping MongoDB find data quickly -> Option B

4. Quick Check:

   Indexes improve query speed = A [OK]

Hint: Indexes speed up data search, not storage or security [OK]

Common Mistakes:

• Thinking indexes store more data
• Confusing indexes with backups
• Assuming indexes encrypt data

2. Which MongoDB command lists all indexes on a collection named users?

easy

A. db.users.showIndexes()

B. db.users.getIndex()

C. db.users.findIndexes()

D. db.users.listIndexes()

Solution

1. Step 1: Recall the correct method to list indexes

   The correct MongoDB method to list indexes on a collection is listIndexes().

2. Step 2: Check each option's validity

   Only db.users.listIndexes() is a valid MongoDB command; others are incorrect or do not exist.

3. Final Answer:

   db.users.listIndexes() -> Option D

4. Quick Check:

   List indexes command = listIndexes() [OK]

Hint: Use listIndexes() to see all indexes on a collection [OK]

Common Mistakes:

• Using getIndex() which is not a MongoDB method
• Trying findIndexes() which does not exist
• Confusing showIndexes() with listIndexes()

3. Given the query db.orders.find({ customerId: 123 }) and the collection has no index on customerId, what will explain('executionStats') likely show?

medium

A. A COLLSCAN (collection scan) with high number of documents examined

B. An IXSCAN (index scan) using the customerId index

C. A syntax error because explain cannot be used here

D. No documents returned because no index exists

Solution

1. Step 1: Understand query behavior without index

   Without an index on customerId, MongoDB must scan the entire collection (COLLSCAN) to find matching documents.

2. Step 2: Interpret explain output

   The explain('executionStats') will show a COLLSCAN and a high number of documents examined, indicating no index was used.

3. Final Answer:

   A COLLSCAN (collection scan) with high number of documents examined -> Option A

4. Quick Check:

   No index means COLLSCAN = B [OK]

Hint: No index means full collection scan in explain output [OK]

Common Mistakes:

• Expecting an index scan without an index
• Thinking explain causes syntax error
• Assuming no documents returned without index

4. You notice a slow query on db.products.find({ category: 'books', price: { $lt: 20 } }). The collection has an index only on category. What is the best fix?

medium

A. Rewrite the query to remove the price filter

B. Remove the existing index on category

C. Add a compound index on { category: 1, price: 1 }

D. Add an index only on price

Solution

1. Step 1: Analyze existing index and query filters

   The query filters on both category and price, but only category is indexed, so the price filter is not optimized.

2. Step 2: Choose the best index to optimize query

   A compound index on { category: 1, price: 1 } allows MongoDB to efficiently filter by both fields, speeding up the query.

3. Final Answer:

   Add a compound index on { category: 1, price: 1 } -> Option C

4. Quick Check:

   Compound index matches query fields = D [OK]

Hint: Use compound indexes for queries filtering multiple fields [OK]

Common Mistakes:

• Removing useful indexes
• Adding index on only one field when query filters multiple
• Changing query to remove filters instead of indexing

5. A MongoDB collection logs has slow queries filtering by userId and sorting by timestamp. Which index best improves performance?

hard

A. A compound index on { userId: 1, timestamp: -1 }

B. An index on { timestamp: 1 }

C. An index on { userId: 1 }

D. No index needed; increase RAM instead

Solution

1. Step 1: Understand query pattern with filter and sort

   The query filters by userId and sorts by timestamp, so both fields affect performance.

2. Step 2: Select index that supports filter and sort

   A compound index on { userId: 1, timestamp: -1 } supports efficient filtering by userId and sorting by timestamp in descending order.

3. Final Answer:

   A compound index on { userId: 1, timestamp: -1 } -> Option A

4. Quick Check:

   Compound index matches filter and sort = C [OK]

Hint: Compound index with sort field improves filter + sort queries [OK]

Common Mistakes:

• Using single-field index ignoring sort
• Choosing wrong sort order in index
• Ignoring indexing and blaming hardware