Compound index and field order in MongoDB - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Effect of field order in compound index on query performance

Consider a MongoDB collection with a compound index on { age: 1, score: -1 }. Which query will efficiently use this index?

MongoDB

db.collection.find({ age: 25, score: { $gte: 80 } })

A The query uses the index efficiently because it matches the prefix fields in order.

B The query does not use the index because the order of fields in the query is reversed.

C The query uses the index only for the <code>score</code> field, ignoring <code>age</code>.

D The query cannot use the index because it uses a range query on <code>score</code>.

💡 Hint

Think about how MongoDB uses the prefix of the compound index fields in order.

MongoDB uses the compound index starting from the first field. Since the query filters on age first and then on score, it can use the index efficiently.

🧠 Conceptual

intermediate

Understanding field order in compound indexes

Why does the order of fields in a MongoDB compound index matter?

A Because MongoDB ignores the order and uses all fields equally in any query.

B Because MongoDB can only use the index if queries filter on the first field(s) in the order defined.

C Because the order determines the storage size of the index but not query usage.

D Because the order affects only the write speed, not the read speed.

💡 Hint

Think about how indexes are structured and how queries match them.

The order matters because MongoDB uses the index starting from the first field. If a query does not filter on the first field(s), the index cannot be used efficiently.

📝 Syntax

advanced

Correct syntax for creating a compound index with field order

Which of the following commands correctly creates a compound index on lastName ascending and age descending in MongoDB?

A db.users.createIndex({ lastName: 1, age: -1 })

B db.users.createIndex(["lastName", 1], ["age", -1])

C db.users.createIndex({ age: -1, lastName: 1 })

D db.users.createIndex({ lastName: "asc", age: "desc" })

💡 Hint

Remember the syntax for createIndex uses a single object with field names and directions.

The correct syntax uses a single object with field names as keys and 1 or -1 as values to indicate ascending or descending order.

❓ optimization

advanced

Optimizing queries with compound index field order

You have a compound index on { city: 1, status: 1 }. Which query will NOT use this index efficiently?

Options:
A) db.collection.find({ city: "NY", status: "active" })
B) db.collection.find({ status: "active" })
C) db.collection.find({ city: "NY" })
D) db.collection.find({ city: "NY", status: { $in: ["active", "pending"] } })

A Query filters on both city and status, uses index efficiently.

B Query filters only on city, uses index efficiently.

C Query filters only on status, cannot use index efficiently.

D Query filters on city and status with $in, uses index efficiently.

💡 Hint

Recall that the index can only be used efficiently if the query filters on the first field in the index.

Since the index starts with city, queries filtering only on status cannot use the index efficiently.

🔧 Debug

expert

Diagnosing why a compound index is not used

A MongoDB collection has a compound index on { category: 1, price: 1 }. A query db.products.find({ price: { $lt: 100 } }) is slow and does not use the index. Why?

A Because range queries on <code>price</code> prevent index usage.

B Because the query uses a comparison operator which MongoDB does not support in indexes.

C Because the index fields are in ascending order and the query needs descending order.

D Because the query does not filter on the first field <code>category</code>, so the index is not used.

💡 Hint

Think about how MongoDB uses compound indexes and the importance of filtering on the first field.

MongoDB requires queries to filter on the first field of a compound index to use it efficiently. Since the query filters only on price (second field), the index is not used.

Practice

1. What does a compound index in MongoDB do?

easy

A. Speeds up queries that filter or sort on multiple fields together

B. Stores data in multiple collections

C. Automatically backs up the database

D. Encrypts data for security

Solution

1. Step 1: Understand what an index is

   An index helps MongoDB find data faster by organizing it.

2. Step 2: Understand compound index purpose

   A compound index uses more than one field to speed up queries that use those fields together.

3. Final Answer:

   Speeds up queries that filter or sort on multiple fields together -> Option A

4. Quick Check:

   Compound index = Speeds up multi-field queries [OK]

Hint: Compound index = multiple fields speed up queries [OK]

Common Mistakes:

• Thinking it stores data in multiple collections
• Confusing with backup or encryption features
• Believing it only indexes one field

2. Which is the correct way to create a compound index on fields age ascending and score descending in MongoDB?

easy

A. db.collection.createIndex({ age: 1, score: -1 })

B. db.collection.createIndex({ age: -1, score: 1 })

C. db.collection.createIndex(["age", "score"])

D. db.collection.createIndex({ age: 'asc', score: 'desc' })

Solution

1. Step 1: Recall syntax for createIndex

   MongoDB uses an object with field names and 1 or -1 for ascending/descending.

2. Step 2: Match field order and directions

   Age ascending is 1, score descending is -1, so { age: 1, score: -1 } is correct.

3. Final Answer:

   db.collection.createIndex({ age: 1, score: -1 }) -> Option A

4. Quick Check:

   Use 1/-1 for asc/desc in object [OK]

Hint: Use {field: 1/-1} object for compound index [OK]

Common Mistakes:

• Using array instead of object for fields
• Using strings like 'asc' or 'desc' instead of 1/-1
• Mixing ascending and descending incorrectly

3. Given a compound index on { city: 1, rating: -1 }, which query will use the index efficiently?

medium

A. db.collection.find({ rating: { $gt: 4 } })

B. db.collection.find({ city: 'NYC' }).sort({ rating: 1 })

C. db.collection.find({ city: 'NYC' }).sort({ rating: -1 })

D. db.collection.find({ rating: -1, city: 'NYC' })

Solution

1. Step 1: Understand index field order and directions

   The index is on city ascending, then rating descending.

2. Step 2: Check query filter and sort usage

   Query B filters on city (first field) and sorts rating descending (matching index direction).

3. Final Answer:

   db.collection.find({ city: 'NYC' }).sort({ rating: -1 }) -> Option C

4. Quick Check:

   Filter first field, sort second field matching index [OK]

Hint: Filter first field, sort second field matching index order [OK]

Common Mistakes:

• Sorting rating ascending when index is descending
• Filtering only on rating without city
• Ignoring field order in index

4. You created a compound index { a: 1, b: 1 } but your query { b: 5 } is slow. What is the likely issue?

medium

A. MongoDB does not support compound indexes

B. The query does not filter on the first indexed field a

C. The index fields are in wrong order; should be { b: 1, a: 1 }

D. The index must use -1 for descending order

Solution

1. Step 1: Recall how compound indexes work

   Compound indexes work best when queries filter on the first field in the index.

2. Step 2: Analyze query filter

   The query filters only on b, the second field, so index on a then b is not used efficiently.

3. Final Answer:

   The query does not filter on the first indexed field a -> Option B

4. Quick Check:

   Filter must include first index field [OK]

Hint: Query must filter first indexed field to use compound index [OK]

Common Mistakes:

• Thinking MongoDB doesn't support compound indexes
• Believing field order doesn't matter
• Assuming descending order is always required

5. You want to optimize queries filtering by category and sorting by price ascending. Which compound index is best?

hard

A. db.collection.createIndex({ price: -1, category: 1 })

B. db.collection.createIndex({ price: 1, category: 1 })

C. db.collection.createIndex({ category: -1, price: -1 })

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

Solution

1. Step 1: Identify query pattern

   Queries filter by category and sort by price ascending.

2. Step 2: Choose index field order and direction

   Index should have category first (filter), then price ascending (sort) to match query.

3. Final Answer:

   db.collection.createIndex({ category: 1, price: 1 }) -> Option D

4. Quick Check:

   Filter field first, sort field second with matching order [OK]

Hint: Filter field first, sort field second with matching order [OK]

Common Mistakes:

• Putting sort field before filter field in index
• Using descending order when query sorts ascending
• Ignoring field order importance