Index usage verification in MongoDB - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to check index usage for a query on the 'users' collection.

MongoDB

db.users.find({ age: { $gt: 25 } }).[1]()

Drag options to blanks, or click blank then click option'

Aexplain

Bcount

Caggregate

Dinsert

Attempts:

3 left

💡 Hint

Common Mistakes

Using count() instead of explain() will only return the number of documents.

Using aggregate() or insert() are unrelated to checking index usage.

The explain() method shows how MongoDB executes a query, including index usage.

2fill in blank

medium

Complete the code to get the query planner information from the explain output.

MongoDB

db.orders.find({ status: 'shipped' }).explain().[1]

Drag options to blanks, or click blank then click option'

AexecutionStats

Bcursor

CserverStatus

DqueryPlanner

Attempts:

3 left

💡 Hint

Common Mistakes

Choosing executionStats gives runtime stats, not the plan itself.

cursor and serverStatus are unrelated to query plan details.

The queryPlanner field in the explain output shows the plan MongoDB uses, including index usage.

3fill in blank

hard

Fix the error in the code to correctly check if an index is used for the query on 'products'.

MongoDB

const plan = db.products.find({ price: { $lt: 100 } }).explain().queryPlanner.[1];
print(plan.indexName);

Drag options to blanks, or click blank then click option'

AqueryPlanner

BexecutionStats

CwinningPlan

DserverStatus

Attempts:

3 left

💡 Hint

Common Mistakes

Using executionStats or serverStatus will not provide indexName.

Accessing queryPlanner directly without winningPlan does not give indexName.

The winningPlan inside queryPlanner contains the indexName. Use explain().queryPlanner.winningPlan to access it.

4fill in blank

hard

Fill both blanks to create a query that uses explain to check if the 'email' index is used in the 'customers' collection.

MongoDB

const plan = db.customers.find({ [1]: '[e-mail]' }).explain().[2].winningPlan;
print(plan.indexName);

Drag options to blanks, or click blank then click option'

Aemail

BexecutionStats

CqueryPlanner

Dname

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'name' instead of 'email' as the filter field.

Using executionStats instead of queryPlanner to find indexName.

The query filters by the 'email' field, and explain().queryPlanner.winningPlan contains the index name.

5fill in blank

hard

Fill all three blanks to write code that explains a query on 'inventory' collection, filters by 'category', and prints the index name used.

MongoDB

const plan = db.inventory.find({ [1]: 'electronics' }).explain().[2].[3];
print(plan.indexName);

Drag options to blanks, or click blank then click option'

Acategory

BqueryPlanner

CwinningPlan

DexecutionStats

Attempts:

3 left

💡 Hint

Common Mistakes

Using executionStats instead of queryPlanner or winningPlan.

Using wrong field name for filtering.

The query filters by 'category'. The explain output's queryPlanner contains the winningPlan which has the indexName.

Practice

1. What does the IXSCAN stage in MongoDB's explain('executionStats') output indicate?

easy

A. The query is updating documents.

B. The query is scanning all documents without an index.

C. The query is using an index to find documents.

D. The query has a syntax error.

Solution

1. Step 1: Understand the meaning of IXSCAN

   The IXSCAN stage means MongoDB is scanning an index to find matching documents.

2. Step 2: Compare with other stages

   If the query was scanning all documents, it would show a COLLSCAN stage instead.

3. Final Answer:

   The query is using an index to find documents. -> Option C

4. Quick Check:

   IXSCAN = index used [OK]

Hint: Look for IXSCAN in explain output to confirm index use [OK]

Common Mistakes:

• Confusing IXSCAN with COLLSCAN
• Assuming IXSCAN means a full collection scan
• Thinking IXSCAN means query error

2. Which of the following is the correct way to run an explain plan with execution stats for a MongoDB query on collection users?

easy

A. db.users.find({age: 25}).explain()

B. db.users.find({age: 25}).explain('executionStats')

C. db.users.explain('executionStats').find({age: 25})

D. db.users.find().explain('stats')

Solution

1. Step 1: Recall the correct syntax for explain

   The correct syntax is to call explain('executionStats') on the cursor returned by find().

2. Step 2: Check each option

   db.users.find({age: 25}).explain('executionStats') correctly calls find() then explain('executionStats'). db.users.explain('executionStats').find({age: 25}) calls explain before find, which is invalid. db.users.find({age: 25}).explain() calls explain without argument, which defaults to 'queryPlanner' not 'executionStats'. db.users.find().explain('stats') uses wrong argument 'stats'.

3. Final Answer:

   db.users.find({age: 25}).explain('executionStats') -> Option B

4. Quick Check:

   find().explain('executionStats') correct [OK]

Hint: Call explain on the find() cursor with 'executionStats' argument [OK]

Common Mistakes:

• Calling explain before find()
• Using wrong explain argument
• Omitting parentheses on find()

3. Given the index on { username: 1 } exists, what will the explain('executionStats') output stage be for this query?

db.users.find({username: 'alice'}).explain('executionStats')

medium

A. IXSCAN

B. COLLSCAN

C. FETCH

D. SORT

Solution

1. Step 1: Check if index matches query field

   The query filters on username, and an index on { username: 1 } exists, so MongoDB can use this index.

2. Step 2: Identify the explain stage for index use

   When an index is used, the explain output shows IXSCAN stage indicating index scan.

3. Final Answer:

   IXSCAN -> Option A

4. Quick Check:

   Index on username = IXSCAN [OK]

Hint: If query matches indexed field, expect IXSCAN stage [OK]

Common Mistakes:

• Expecting COLLSCAN despite index presence
• Confusing FETCH with index scan
• Assuming SORT appears without sort query

4. You run db.orders.find({status: 'shipped'}).explain('executionStats') but see COLLSCAN instead of IXSCAN. What is the most likely cause?

medium

A. The query syntax is incorrect.

B. The collection is empty.

C. MongoDB does not support indexes on string fields.

D. There is no index on the status field.

Solution

1. Step 1: Understand COLLSCAN meaning

   COLLSCAN means MongoDB scanned all documents because no suitable index was found.

2. Step 2: Identify cause for no index use

   If the query filters on status but no index exists on that field, MongoDB must scan the whole collection.

3. Final Answer:

   There is no index on the status field. -> Option D

4. Quick Check:

   COLLSCAN = no index on query field [OK]

Hint: COLLSCAN means no index matches query filter [OK]

Common Mistakes:

• Assuming syntax error causes COLLSCAN
• Believing MongoDB can't index strings
• Thinking empty collection causes COLLSCAN

5. You have a compound index on { category: 1, price: -1 }. Which query will use this index efficiently according to explain('executionStats')?

hard

A. db.products.find({category: 'books', price: 20}).sort({price: -1})

B. db.products.find({price: {$lt: 20}})

C. db.products.find({category: 'books'}).sort({price: 1})

D. db.products.find({category: 'books', price: {$lt: 20}})

Solution

1. Step 1: Understand compound index usage

   A compound index on { category: 1, price: -1 } can be used efficiently if the query filters on category first, then optionally on price, and the sort matches the index order.

2. Step 2: Analyze each query

   db.products.find({category: 'books', price: 20}).sort({price: -1}) filters on both category and price with equality and sorts price descending, matching the index order, so it uses the index efficiently. db.products.find({price: {$lt: 20}}) filters only on price, which is the second field, so index is not used efficiently. db.products.find({category: 'books'}).sort({price: 1}) sorts price ascending but index has price descending, so sort won't use index efficiently. db.products.find({category: 'books', price: {$lt: 20}}) filters on both but does not specify sort matching index order, so less efficient than A.

3. Final Answer:

   db.products.find({category: 'books', price: 20}).sort({price: -1}) -> Option A

4. Quick Check:

   Compound index needs prefix field filter and matching sort order [OK]

Hint: Filter on first index field and match sort order to use compound index efficiently [OK]

Common Mistakes:

• Filtering only on second index field
• Ignoring sort direction in compound index
• Assuming any filter uses compound index fully