Compound index and field order in MongoDB - Mini Project: Build & Apply

Creating and Using Compound Indexes in MongoDB

📖 Scenario: You are managing a MongoDB database for a small online bookstore. You want to speed up searches that look for books by both author and year of publication.

🎯 Goal: Build a compound index on the author and year fields in the books collection to improve query performance.

📋 What You'll Learn

Create a books collection with sample documents

Define a compound index on author and year fields

Understand the importance of field order in compound indexes

Query the collection using the compound index

💡 Why This Matters

🌍 Real World

Compound indexes help speed up searches that filter on multiple fields, common in real-world apps like bookstores, social media, and e-commerce.

💼 Career

Understanding compound indexes is essential for database administrators and backend developers to optimize query performance and resource usage.

1

Create the books collection with sample data

Create a collection called books and insert these exact documents: { title: "Book A", author: "Alice", year: 2020 }, { title: "Book B", author: "Bob", year: 2019 }, and { title: "Book C", author: "Alice", year: 2018 }.

MongoDB

// Insert the sample documents into the books collection
// Your code here

Hint

Use db.books.insertMany() with an array of documents.

2

Define a compound index on author and year

Create a compound index on the books collection with author as the first field and year as the second field, both in ascending order.

MongoDB

db.books.insertMany([
  { title: "Book A", author: "Alice", year: 2020 },
  { title: "Book B", author: "Bob", year: 2019 },
  { title: "Book C", author: "Alice", year: 2018 }
])
// Create the compound index on author and year
// Your code here

Hint

Use db.books.createIndex() with an object specifying author: 1 and year: 1.

3

Query using the compound index

Write a query to find all books where author is "Alice" and year is greater than or equal to 2019.

MongoDB

db.books.insertMany([
  { title: "Book A", author: "Alice", year: 2020 },
  { title: "Book B", author: "Bob", year: 2019 },
  { title: "Book C", author: "Alice", year: 2018 }
])
db.books.createIndex({ author: 1, year: 1 })
// Query books by author Alice and year >= 2019
// Your code here

Hint

Use db.books.find() with a query object specifying author: "Alice" and year: { $gte: 2019 }.

4

Create a compound index with reversed field order

Create another compound index on the books collection with year as the first field and author as the second field, both in ascending order.

MongoDB

db.books.insertMany([
  { title: "Book A", author: "Alice", year: 2020 },
  { title: "Book B", author: "Bob", year: 2019 },
  { title: "Book C", author: "Alice", year: 2018 }
])
db.books.createIndex({ author: 1, year: 1 })
db.books.find({ author: "Alice", year: { $gte: 2019 } })
// Create a compound index with year first and author second
// Your code here

Hint

Use db.books.createIndex() with { year: 1, author: 1 }.

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