Covered queries with indexes in MongoDB - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of a covered query using a compound index

Given a MongoDB collection products with documents containing category, price, and name fields, and a compound index on { category: 1, price: 1 }, what will be the output of the following query?

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

Assuming the collection has these documents:

[{ category: 'books', price: 10, name: 'Book A' }, { category: 'books', price: 15, name: 'Book B' }, { category: 'electronics', price: 100, name: 'Gadget' }]

MongoDB

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

A [{ category: 'books', price: 10 }, { category: 'books', price: 15 }]

B [{ category: 'books', price: 10, name: 'Book A' }, { category: 'books', price: 15, name: 'Book B' }]

C [{ category: 'books' }, { category: 'books' }]

D [{ price: 10 }, { price: 15 }]

💡 Hint

Remember that a covered query returns only the fields in the index and the projection.

The query filters by category: 'books' and projects only category and price. Since the compound index covers these fields, MongoDB can return the results without fetching the full documents. The name field is not included in the projection, so it is not returned.

🧠 Conceptual

intermediate

Understanding covered queries and index fields

Which of the following statements about covered queries in MongoDB is TRUE?

A A covered query can return fields not included in the index if they are in the projection.

B Covered queries always require scanning the entire collection.

C A covered query requires all fields in the query filter and projection to be in the index.

D Covered queries do not improve query performance.

💡 Hint

Think about what fields the index must contain for MongoDB to avoid fetching documents.

For a query to be covered, all fields used in the filter and the projection must be included in the index. This allows MongoDB to answer the query using only the index without reading the full documents.

📝 Syntax

advanced

Identify the correct index definition for a covered query

You want to create an index on the users collection to cover this query:

db.users.find({ age: { $gte: 18 } }, { age: 1, name: 1, _id: 0 })

Which index definition will allow this query to be covered?

A { age: 1, email: 1 }

B { age: 1, name: 1 }

C { name: 1 }

D { age: 1 }

💡 Hint

The index must include all fields used in the filter and projection.

The query filters on age and projects age and name. To cover the query, the index must include both age and name. Option B defines such an index.

❓ optimization

advanced

Optimizing a query with covered indexes

You have a collection orders with fields customerId, status, and total. You run this query:

db.orders.find({ status: 'shipped' }, { customerId: 1, total: 1, _id: 0 })

Which index will best optimize this query as a covered query?

A { status: 1 }

B { total: 1, status: 1 }

C { customerId: 1, total: 1 }

D { status: 1, customerId: 1, total: 1 }

💡 Hint

The index must include all fields in the filter and projection, with the filter field first.

The query filters on status and projects customerId and total. To be covered, the index must include status first (for filtering), then customerId and total for projection. Option D satisfies this.

🔧 Debug

expert

Why is this query not covered despite the index?

You created this index on employees collection:

{ department: 1, salary: 1 }

And run this query:

db.employees.find({ department: 'HR' }, { department: 1, salary: 1, name: 1, _id: 0 })

Why is this query NOT covered?

A Because the projection includes <code>name</code> which is not in the index.

B Because the filter on <code>department</code> is not supported by the index.

C Because the index does not include the <code>_id</code> field.

D Because the query does not specify <code>_id: 1</code> in the projection.

💡 Hint

Check if all projected fields are in the index.

The query projects name which is not part of the index. For a query to be covered, all projected fields must be in the index. Since name is missing, MongoDB must fetch the full document, so the query is not covered.

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