Index usage verification in MongoDB - Step-by-Step Execution

Concept Flow - Index usage verification

Run Query

↓

MongoDB Query Planner

↓

Check Available Indexes

↓

Choose Index or Collection Scan

↓

Execute Query Using Chosen Plan

↓

Explain Output Shows Index Usage

This flow shows how MongoDB decides to use an index or not when running a query and how to verify it using explain.

Execution Sample

MongoDB

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

This command runs a query to find users aged 30 and shows detailed stats including index usage.

Execution Table

Step	Action	Query Planner Decision	Index Used	Output Detail
1	Run query db.users.find({age: 30})	Analyzing query	N/A	Preparing to plan
2	Check indexes on 'age'	Indexes found: age_1	age_1	Index available for 'age' field
3	Choose plan	Index scan chosen over collection scan	age_1	Using index to filter documents
4	Execute query	Index scan performed	age_1	Documents matching age=30 retrieved
5	Return explain output	Shows execution stats	age_1	Index used: true, keys examined, docs returned
6	End	Query complete	age_1	Index usage verified in explain output

💡 Query execution finished with index usage confirmed by explain output

Variable Tracker

Variable	Start	After Step 2	After Step 3	After Step 4	Final
Index Used	None	age_1 found	age_1 chosen	age_1 used	age_1 confirmed
Query Plan	None	Indexes checked	Index scan plan	Executing index scan	Plan executed

Key Moments - 2 Insights

Why does the explain output show 'COLLSCAN' instead of an index name?

If the explain output shows 'COLLSCAN', it means MongoDB did not use any index and scanned the whole collection. This can happen if no suitable index exists or the query does not match any index. See execution_table row 3 where index choice is made.

How can I tell from explain output that an index was actually used?

Look for 'indexName' field and 'stage' showing 'IXSCAN' in the explain output. In execution_table row 5, the output detail confirms index usage with keys examined and index name.

Visual Quiz - 3 Questions

Look at the execution_table, at which step does MongoDB decide which index to use?

A Step 3

B Step 4

C Step 2

D Step 5

💡 Hint

Check the 'Query Planner Decision' column in execution_table row 3.

According to variable_tracker, what is the value of 'Index Used' after Step 4?

A age_1 found

B age_1 used

C None

D age_1 confirmed

💡 Hint

Look at the 'Index Used' row and the 'After Step 4' column in variable_tracker.

If no index exists on the queried field, what would the execution_table show at Step 3?

A Index scan chosen over collection scan

B Query aborted

C Collection scan chosen due to no index

D Index created automatically

💡 Hint

Refer to key_moments explanation about 'COLLSCAN' and execution_table Step 3.

Concept Snapshot

MongoDB uses indexes to speed up queries.
Use db.collection.find(query).explain('executionStats') to see if an index is used.
Explain output shows query plan and index usage.
If no index matches, MongoDB does a collection scan.
Check 'stage' and 'indexName' in explain to verify index usage.

Full Transcript

This visual execution shows how MongoDB verifies index usage when running a query. First, the query runs and the query planner checks available indexes. If an index matches the query, MongoDB chooses an index scan plan instead of scanning the whole collection. The query executes using the chosen index. The explain output then shows detailed execution stats including whether an index was used. Variables like 'Index Used' track the index state through each step. Key moments clarify why sometimes MongoDB does not use an index and how to read the explain output to confirm index usage. The quiz tests understanding of when and how MongoDB picks and uses indexes during query execution.

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