Identifying missing indexes in MongoDB - Step-by-Step Execution

Concept Flow - Identifying missing indexes

Run slow query

↓

Check query plan

↓

Look for COLLSCAN (collection scan)?

No→Index used, no missing index

Yes↓

Analyze query fields

↓

Suggest index on those fields

↓

Create index

↓

Query runs faster

This flow shows how to find queries that do not use indexes and then create indexes to speed them up.

Execution Sample

MongoDB

db.collection.find({age: {$gt: 30}}).explain('executionStats')

This command runs a query and shows how MongoDB executes it, including whether it uses an index.

Execution Table

Step	Action	Query Plan Detail	Index Used?	Result
1	Run query with explain	COLLSCAN	No	Full collection scan, slow
2	Identify filter field	Filter on 'age' field	N/A	No index on 'age'
3	Suggest index	Create index on 'age'	N/A	Index created
4	Run query again with explain	IXSCAN on 'age'	Yes	Index used, faster query
5	Exit	Query optimized	Yes	Execution stops

💡 After creating index on 'age', query uses index and runs faster, so no missing index remains

Variable Tracker

Variable	Start	After Step 1	After Step 3	After Step 4	Final
Index on 'age'	None	None	Created	Created	Created
Query plan	N/A	COLLSCAN	N/A	IXSCAN	IXSCAN

Key Moments - 2 Insights

Why does the query show COLLSCAN in the plan at first?

Because there is no index on the 'age' field yet, MongoDB scans the whole collection (COLLSCAN) as shown in execution_table step 1.

How do we know the index creation fixed the problem?

After creating the index, the query plan changes to IXSCAN (index scan) in step 4, meaning the index is used and the query is faster.

Visual Quiz - 3 Questions

Look at the execution_table, what is the query plan detail at step 1?

A COLLSCAN

B IXSCAN

C FETCH

D SORT

💡 Hint

Check the 'Query Plan Detail' column in execution_table row with Step 1

At which step is the index on 'age' created?

A Step 1

B Step 2

C Step 3

D Step 4

💡 Hint

Look at the 'Index Used?' and 'Action' columns in execution_table for index creation

If the index was not created, what would the query plan show at step 4?

A IXSCAN

B COLLSCAN

C FETCH

D SORT

💡 Hint

Refer to execution_table step 1 and 4 to see difference after index creation

Concept Snapshot

Identifying missing indexes in MongoDB:
- Run query with .explain('executionStats')
- Check if query plan shows COLLSCAN (collection scan)
- If yes, no index is used on filter fields
- Create index on those fields
- Query then uses IXSCAN (index scan) and runs faster

Full Transcript

To identify missing indexes in MongoDB, first run your query with explain('executionStats') to see how MongoDB executes it. If the query plan shows COLLSCAN, it means MongoDB scans the whole collection because no suitable index exists. Next, analyze which fields are used in the query filter and create an index on those fields. After creating the index, run the query again with explain. Now the query plan should show IXSCAN, meaning the index is used and the query runs faster. This process helps find and fix missing indexes to improve query performance.