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.