Covered queries with indexes in MongoDB - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create an index on the 'age' field.

MongoDB

db.users.createIndex([1]: 1})

Drag options to blanks, or click blank then click option'

Aemail

Bname

Cage

Daddress

Attempts:

3 left

💡 Hint

Common Mistakes

Choosing a field not used in the query filter.

Using the wrong field name.

The index is created on the 'age' field to optimize queries filtering by age.

2fill in blank

medium

Complete the query to find users aged 30 using the index.

MongoDB

db.users.find([1]: 30})

Drag options to blanks, or click blank then click option'

Aage

Bemail

Cname

Daddress

Attempts:

3 left

💡 Hint

Common Mistakes

Filtering by a non-indexed field.

Using a field name that does not exist.

The query filters users by the 'age' field, which is indexed for faster lookup.

3fill in blank

hard

Fix the error in the index creation by choosing the correct index type for a covered query.

MongoDB

db.users.createIndex([1]: 1})

Drag options to blanks, or click blank then click option'

Aaddress

Bname

Cemail

Dage

Attempts:

3 left

💡 Hint

Common Mistakes

Using an unrelated field for the index.

Not including the fields needed for the covered query.

Covered queries require the index to include the fields used in the query and projection. Indexing 'age' supports this.

4fill in blank

hard

Fill both blanks to create a compound index on 'age' and 'name' for a covered query.

MongoDB

db.users.createIndex([1]: 1, [2]: 1})

Drag options to blanks, or click blank then click option'

Aage

Bemail

Cname

Daddress

Attempts:

3 left

💡 Hint

Common Mistakes

Choosing fields not used together in queries.

Creating single-field indexes instead of compound.

A compound index on 'age' and 'name' supports queries filtering and projecting these fields, enabling covered queries.

5fill in blank

hard

Fill all three blanks to write a covered query that uses the compound index on 'age' and 'name' and projects only 'name'.

MongoDB

db.users.find([1]: 25}, [2]: 1, [3]: 0})

Drag options to blanks, or click blank then click option'

Aage

Bname

Cemail

D_id

Attempts:

3 left

💡 Hint

Common Mistakes

Projecting fields not in the index.

Not excluding '_id' when needed for covered queries.

The query filters by 'age', projects 'name', and excludes '_id' to use the compound index for a covered query.

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