Covered queries with indexes in MongoDB - Step-by-Step Execution

Concept Flow - Covered queries with indexes

Query issued

↓

Check index for fields

↓

Index covers all query fields?

No→Fetch full document from collection

Yes↓

Return results directly from index

↓

Query completes faster

The database checks if the index contains all fields needed by the query. If yes, it returns data from the index only, skipping the full document fetch.

Execution Sample

MongoDB

db.users.createIndex({name:1, age:1, city:1})
db.users.find({name:'Alice'}, {age:1, city:1, _id:0})

Create an index on name, age, city fields and run a query that requests only those fields, enabling a covered query.

Execution Table

Step	Action	Index Fields Checked	Query Fields	Covered?	Data Source	Result
1	Query issued	name, age, city	name, age, city	Yes	Index only	{age: 30, city: 'NY'}
2	Return result	name, age, city	age, city	Yes	Index only	Result sent to client
3	Query ends	-	-	-	-	No need to fetch full document

💡 Query fields are fully covered by the index, so no document fetch is needed.

Variable Tracker

Variable	Start	After Step 1	After Step 2	Final
Index Fields	none	name, age, city	name, age, city	name, age, city
Query Fields	none	name, age, city	age, city	age, city
Data Source	none	Index only	Index only	Index only

Key Moments - 2 Insights

Why does the query not fetch the full document?

Because the index contains all the fields requested by the query, the database can return results directly from the index without fetching the full document (see execution_table step 1).

What happens if the query requests a field not in the index?

The database must fetch the full document from the collection to get the missing field, so the query is not covered (not shown here but implied by the 'No' branch in concept_flow).

Visual Quiz - 3 Questions

Look at the execution_table, at which step does the database decide the query is covered by the index?

A Step 1

B Step 2

C Step 3

D Before Step 1

💡 Hint

Check the 'Covered?' column in execution_table row for Step 1.

According to variable_tracker, what is the data source after Step 2?

A Full document fetch

B Index only

C No data source

D Partial index and partial document

💡 Hint

Look at the 'Data Source' row under 'After Step 2' in variable_tracker.

If the query requested a field not in the index, what would change in the execution_table?

A The query would still be covered

B The index fields would change

C The 'Covered?' column would say 'No' and data source would be 'Full document fetch'

D The query would return no results

💡 Hint

Refer to the concept_flow where the 'No' branch leads to fetching the full document.

Concept Snapshot

Covered queries use indexes that include all requested fields.
If all query fields are in the index, MongoDB returns data from the index only.
This avoids fetching full documents, making queries faster.
Create indexes including all fields you want to query and return.
Covered queries improve performance by reducing disk reads.

Full Transcript

Covered queries with indexes happen when the database can answer a query using only the index without fetching the full document. This requires the index to contain all fields the query filters on and returns. When a query is issued, MongoDB checks if the index covers all needed fields. If yes, it returns results directly from the index, speeding up the query. If not, it fetches the full document. Creating indexes that cover your query fields enables covered queries and improves performance.

Practice

1. What is a covered query in MongoDB?

easy

A. A query that only returns the document _id field

B. A query that scans all documents in a collection

C. A query that updates documents without using indexes

D. A query that uses an index to return all requested fields without reading the full document

Solution

1. Step 1: Understand what a covered query means

   A covered query uses an index that contains all the fields needed for the query, so MongoDB does not need to fetch the full document from the collection.

2. Step 2: Identify the correct description

   A query that uses an index to return all requested fields without reading the full document correctly describes a covered query as one that returns all requested fields from the index alone, improving speed and resource use.

3. Final Answer:

   A query that uses an index to return all requested fields without reading the full document -> Option D

4. Quick Check:

   Covered query = uses index only [OK]

Hint: Covered queries return data only from indexes, no document fetch [OK]

Common Mistakes:

• Thinking covered queries scan full documents
• Confusing covered queries with index-only updates
• Assuming covered queries only return _id field

2. Which MongoDB index definition supports a covered query for filtering on status and returning status and score fields?

easy

A. { status: 1, score: 1 }

B. { score: 1 }

C. { status: 1 }

D. { _id: 1, status: 1 }

Solution

1. Step 1: Identify fields needed for covered query

   The query filters on status and returns status and score. The index must include all these fields.

2. Step 2: Choose index covering all fields

   { status: 1, score: 1 } includes both status and score, so it can cover the query without fetching documents.

3. Final Answer:

   { status: 1, score: 1 } -> Option A

4. Quick Check:

   Index includes filter and return fields [OK]

Hint: Index must include all filter and return fields for coverage [OK]

Common Mistakes:

• Using index with only filter or only return fields
• Assuming _id is always included in covered queries
• Ignoring order of fields in index for coverage

3. Given the index { category: 1, price: 1 } on a collection, what will be the output of this query?

db.products.find({ category: 'books' }, { category: 1, price: 1, _id: 0 })

medium

A. Error because _id is excluded

B. Documents with all fields because index is ignored

C. Documents with only category and price fields, fetched from index (covered query)

D. Documents with category only, price missing

Solution

1. Step 1: Analyze the index and query fields

   The index covers category and price. The query filters on category and returns category and price only, excluding _id.

2. Step 2: Determine if query is covered

   Since all requested fields are in the index, MongoDB can return results from the index without fetching full documents, making it a covered query.

3. Final Answer:

   Documents with only category and price fields, fetched from index (covered query) -> Option C

4. Quick Check:

   Index covers filter and projection fields [OK]

Hint: Projection fields must be in index for covered query output [OK]

Common Mistakes:

• Assuming _id exclusion causes error
• Thinking full documents are always returned
• Ignoring index coverage for projection

4. You have an index { user: 1 } but your query is:

db.users.find({ user: 'alice' }, { age: 1, _id: 0 })

Why is this query NOT a covered query?

medium

A. Because the index does not include the age field in the index keys

B. Because the query excludes _id which is always required

C. Because the index does not include the age field in the projection

D. Because the query filters on user but projection excludes it

Solution

1. Step 1: Check index fields and query projection

   The index has only user. The query filters on user but only returns age, excluding _id.

2. Step 2: Understand covered query requirements

   For a covered query, the index must include all returned fields in the projection. Since age is not in the index, MongoDB must fetch the full document.

3. Final Answer:

   Because the index does not include the age field in the index keys -> Option A

4. Quick Check:

   Index must include all projection fields [OK]

Hint: All projected fields must be in the index for coverage [OK]

Common Mistakes:

• Thinking _id exclusion breaks coverage
• Assuming index on filter field covers all projections
• Thinking filter fields must appear in projection

5. You want to optimize this query for coverage:

db.orders.find({ status: 'shipped', total: { $gt: 100 } }, { status: 1, total: 1, customer: 1, _id: 0 })

Which index will allow this query to be covered?

hard

A. { status: 1, total: 1 }

B. { status: 1, total: 1, customer: 1 }

C. { total: 1, customer: 1 }

D. { status: 1, customer: 1 }

Solution

1. Step 1: Identify filter and projection fields

   The query filters on status and total, and returns status, total, and customer.

2. Step 2: Choose index covering all fields

   To cover the query, the index must include all filter and returned fields: status, total, and customer. { status: 1, total: 1, customer: 1 } includes all three.

3. Final Answer:

   { status: 1, total: 1, customer: 1 } -> Option B

4. Quick Check:

   Index covers filter + projection fields [OK]

Hint: Index must include all filter and returned fields for coverage [OK]

Common Mistakes:

• Leaving out returned fields from index
• Assuming filter fields alone suffice
• Ignoring order of fields in index