Index intersection behavior in MongoDB - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Index Intersection Query Result

Given a MongoDB collection products with indexes on category and price, what documents will be returned by this query?

{ category: "electronics", price: { $lt: 100 } }

Assume documents:

[{_id:1, category:"electronics", price: 90}, {_id:2, category:"electronics", price: 150}, {_id:3, category:"furniture", price: 80}]

MongoDB

db.products.find({ category: "electronics", price: { $lt: 100 } })

A [{_id:1, category:"electronics", price: 90}]

B [{_id:1, category:"electronics", price: 90}, {_id:2, category:"electronics", price: 150}]

C [{_id:3, category:"furniture", price: 80}]

D []

💡 Hint

Think about which documents satisfy both conditions.

The query filters documents where category is "electronics" AND price is less than 100. Only the first document meets both.

🧠 Conceptual

intermediate

Understanding Index Intersection

What does MongoDB's index intersection feature do?

A Combines multiple single-field indexes to satisfy a query with multiple conditions.

B Removes unused indexes from the database.

C Creates a new compound index automatically for every query.

D Merges documents from different collections.

💡 Hint

Think about how MongoDB uses indexes to speed up queries with multiple filters.

Index intersection allows MongoDB to use multiple single-field indexes together to answer queries with multiple conditions efficiently.

📝 Syntax

advanced

Correct Index Creation Syntax

Which option correctly creates two single-field indexes on category and price in MongoDB?

A db.products.createIndex(["category", "price"])

B db.products.createIndex({ category: "asc", price: "asc" })

C db.products.createIndex({ category: 1 }); db.products.createIndex({ price: 1 })

D db.products.createIndex({ category: 1, price: 1 })

💡 Hint

Remember how to create single-field indexes separately.

Option C creates two separate single-field indexes, one on category and one on price. Option C creates a compound index. Options B and C use invalid syntax.

❓ optimization

advanced

Improving Query Performance with Index Intersection

You have a query filtering on status and date. You currently have single-field indexes on both fields. Which action will most likely improve query performance?

A Add a single-field index on an unrelated field.

B Drop both single-field indexes and rely on collection scan.

C Create a text index on <code>status</code>.

D Create a compound index on <code>{ status: 1, date: 1 }</code>.

💡 Hint

Think about how compound indexes compare to index intersection.

A compound index on both fields can be more efficient than index intersection because it stores combined keys, reducing the need to merge results.

🔧 Debug

expert

Diagnosing Unexpected Query Performance

A query filtering on type and region is slow despite having single-field indexes on both. Which is the most likely cause?

A The single-field indexes are compound indexes, causing conflicts.

B MongoDB is not using index intersection because the query uses an $or operator incorrectly.

C The collection is sharded, disabling index intersection.

D The query uses a $text search, which ignores indexes.

💡 Hint

Consider how $or affects index usage.

MongoDB cannot use index intersection for $or queries if the conditions are on different fields in separate clauses. This can cause slower performance.

Practice

1. What does index intersection in MongoDB allow the database to do?

easy

A. Combine multiple indexes to answer a single query

B. Create a new index automatically

C. Merge two collections into one

D. Delete unused indexes to save space

Solution

1. Step 1: Understand the purpose of index intersection

   Index intersection allows MongoDB to use more than one index together to satisfy a query efficiently.

2. Step 2: Differentiate from other index operations

   It does not create or delete indexes, nor merge collections; it only combines existing indexes for query optimization.

3. Final Answer:

   Combine multiple indexes to answer a single query -> Option A

4. Quick Check:

   Index intersection = combining indexes [OK]

Hint: Index intersection means using multiple indexes together [OK]

Common Mistakes:

• Thinking index intersection creates new indexes
• Confusing index intersection with collection merging
• Assuming it deletes indexes automatically

2. Which of the following is the correct way to check if MongoDB used index intersection for a query?

easy

A. Check the size of the indexes with db.collection.indexSize()

B. Run db.collection.stats() to see index usage

C. Use db.collection.aggregate() without any options

D. Use db.collection.find().explain() and look for indexIntersection in the plan

Solution

1. Step 1: Identify the method to analyze query plans

   The explain() method shows how MongoDB executes a query, including index usage.

2. Step 2: Look for index intersection details

   Within the explain output, the indexIntersection field indicates if multiple indexes were combined.

3. Final Answer:

   Use db.collection.find().explain() and look for indexIntersection in the plan -> Option D

4. Quick Check:

   Explain() shows index intersection [OK]

Hint: Use explain() to see index intersection in query plan [OK]

Common Mistakes:

• Using stats() which shows collection info, not query plan
• Running aggregate() without explain() does not show index use
• Checking index size does not reveal intersection

3. Given a collection with indexes on { age: 1 } and { city: 1 }, what will MongoDB do for this query?

db.users.find({ age: 30, city: "NY" })

medium

A. Use index intersection combining both age and city indexes

B. Use only the index on city

C. Use only the index on age

D. Perform a full collection scan ignoring indexes

Solution

1. Step 1: Analyze the query filter fields

   The query filters on both age and city, and there are separate indexes on each field.

2. Step 2: Understand MongoDB's index intersection behavior

   MongoDB can combine these two indexes to efficiently find documents matching both conditions using index intersection.

3. Final Answer:

   Use index intersection combining both age and city indexes -> Option A

4. Quick Check:

   Query on two indexed fields = index intersection [OK]

Hint: Multiple single-field indexes on query fields trigger index intersection [OK]

Common Mistakes:

• Assuming MongoDB uses only one index per query
• Thinking MongoDB always does full scan if multiple indexes exist
• Confusing index intersection with compound indexes

4. You wrote this query:

db.products.find({ category: "books", price: { $lt: 20 } })

Indexes exist on { category: 1 } and { price: 1 }. But explain() shows no index intersection used. What is a likely cause?

medium

A. MongoDB does not support index intersection on range queries

B. MongoDB prefers a single index that covers both fields if available

C. The query syntax is incorrect for index intersection

D. Index intersection only works with compound indexes

Solution

1. Step 1: Understand MongoDB's index selection logic

   MongoDB chooses the most efficient index plan. If a single index covers both fields, it prefers that over intersection.

2. Step 2: Consider the indexes present

   Since only single-field indexes exist, MongoDB may pick one index if it estimates it is more efficient than combining both.

3. Final Answer:

   MongoDB prefers a single index that covers both fields if available -> Option B

4. Quick Check:

   MongoDB picks best index, not always intersection [OK]

Hint: MongoDB picks best index plan, not always intersection [OK]

Common Mistakes:

• Believing index intersection requires compound indexes
• Thinking query syntax disables index intersection
• Assuming range queries never use index intersection

5. You have a collection with indexes:

{ author: 1 }, { year: 1 }, { genre: 1 }

To optimize this query:

db.books.find({ author: "Alice", year: 2020, genre: "fiction" })

Which index strategy will most likely improve performance using index intersection?

hard

A. Create a compound index on { author: 1, year: 1, genre: 1 }

B. Keep single-field indexes and let MongoDB intersect them

C. Create compound indexes on pairs like { author: 1, year: 1 } and { genre: 1 }

D. Drop all indexes and create a single index on { author: 1 }

Solution

1. Step 1: Understand index intersection limits

   MongoDB can combine multiple single-field indexes, but compound indexes on multiple fields are often more efficient.

2. Step 2: Evaluate index strategies

   A full compound index on all three fields is best but costly to maintain; compound indexes on pairs plus a single index can help MongoDB intersect efficiently.

3. Step 3: Compare options

   Create compound indexes on pairs like { author: 1, year: 1 } and { genre: 1 } creates compound indexes on pairs, enabling better intersection than only single-field indexes, improving query speed without full compound index overhead.

4. Final Answer:

   Create compound indexes on pairs like { author: 1, year: 1 } and { genre: 1 } -> Option C

5. Quick Check:

   Compound pairs + intersection = better performance [OK]

Hint: Compound indexes on pairs plus single indexes improve intersection [OK]

Common Mistakes:

• Assuming only single-field indexes are enough
• Dropping indexes reduces performance
• Believing full compound index is always best