MongoDB Compound Index Practice Questions with Solutions

By /

Introduction

A compound index in MongoDB is an index created using two or more fields. It is useful when queries commonly filter or sort documents using multiple fields together, such as course and age, or category and price. The order of fields in a compound index matters because MongoDB uses the index according to its field order. In this chapter, you will practice creating, querying, sorting, testing, and removing compound indexes. MongoDB Compound Index practice questions with solutions to help you understand the concepts.

Q1. Create a Basic Compound Index

Problem Statement

Create a compound index on the course and age fields of the students collection.

MongoDB Command / Query

db.students.createIndex({
  course: 1,
  age: 1
})

Expected Output

course_1_age_1

This index contains two fields:

  • course in ascending order
  • age in ascending order

The index name is automatically generated from the indexed fields and their sort directions.

Q2. Create a Compound Index with Different Sort Directions

Problem Statement

Create a compound index where course is ascending and age is descending.

MongoDB Command / Query

db.students.createIndex({
  course: 1,
  age: -1
})

Expected Output

course_1_age_-1

A compound index can use different sort directions for different fields.

Q3. Query Using Both Compound Index Fields

Problem Statement

Find students whose course is Python and whose age is greater than 15.

MongoDB Command / Query

db.students.find({
  course: "Python",
  age: { $gt: 15 }
})

If the following compound index exists:

db.students.createIndex({
  course: 1,
  age: 1
})

MongoDB can use this index to support the query.

Expected Output

{
  _id: ObjectId("..."),
  name: "Rahul",
  age: 16,
  course: "Python"
}

The compound index is useful because the query uses both indexed fields.

Q4. Check a Compound Index with getIndexes()

Problem Statement

Display all indexes of the students collection and verify the compound index.

MongoDB Command / Query

db.students.getIndexes()

Expected Output

[
  {
    v: 2,
    key: {
      _id: 1
    },
    name: "_id_"
  },
  {
    v: 2,
    key: {
      course: 1,
      age: 1
    },
    name: "course_1_age_1"
  }
]

The key object shows that both course and age are part of the same index.

Q5. Use a Compound Index for Sorting

Problem Statement

Create a compound index on course and age, then sort students first by course and then by age.

MongoDB Command / Query

Create the index:

db.students.createIndex({
  course: 1,
  age: 1
})

Sort the documents:

db.students.find().sort({
  course: 1,
  age: 1
})

Expected Output

{
  name: "Aman",
  age: 15,
  course: "Java"
}
{
  name: "Neha",
  age: 16,
  course: "Python"
}
{
  name: "Rahul",
  age: 17,
  course: "Python"
}

The result is ordered by course first. When multiple documents have the same course, age determines their order.

Q6. Query Using Only the First Field

Problem Statement

Create a compound index on course and age, then find all students studying Python.

MongoDB Command / Query

Create the index:

db.students.createIndex({
  course: 1,
  age: 1
})

Run the query:

db.students.find({
  course: "Python"
})

Expected Output

{
  name: "Rahul",
  age: 16,
  course: "Python"
}
{
  name: "Neha",
  age: 17,
  course: "Python"
}

MongoDB can use the compound index for a query on the first field, course.

This is related to the prefix behavior of compound indexes.

Q7. Query Using Only the Second Field

Problem Statement

A compound index exists on course and age. Find students whose age is 16.

MongoDB Command / Query

db.students.find({
  age: 16
})

Expected Output

{
  name: "Rahul",
  age: 16,
  course: "Python"
}

Although age is part of the compound index, an index beginning with { course: 1, age: 1 } is not generally the appropriate index for a query that only specifies age.

The field order matters in a compound index. If queries frequently search only by age, a separate index beginning with age may be more appropriate.

Q8. Check Whether a Compound Index Is Used

Problem Statement

Use explain() to inspect the execution plan for a query that uses both course and age.

MongoDB Command / Query

db.students.find({
  course: "Python",
  age: { $gt: 15 }
}).explain("executionStats")

Expected Output

The winning execution plan may contain:

{
  stage: "IXSCAN"
}

IXSCAN indicates that MongoDB is scanning an index.

The execution plan can also show the index that was selected, such as:

{
  indexName: "course_1_age_1"
}

The exact output depends on the collection, available indexes, query, and query planner.

Q9. Create a Compound Index for Products

Problem Statement

Create a compound index on category and price for the products collection. Then find electronics costing more than 1000.

MongoDB Command / Query

Create the index:

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

Run the query:

db.products.find({
  category: "Electronics",
  price: { $gt: 1000 }
})

Expected Output

{
  name: "Laptop",
  category: "Electronics",
  price: 55000
}
{
  name: "Monitor",
  category: "Electronics",
  price: 12000
}

The index contains both category and price, matching the fields used by the query.

Q10. Remove a Compound Index

Problem Statement

Remove the course_1_age_1 compound index from the students collection.

MongoDB Command / Query

db.students.dropIndex("course_1_age_1")

Expected Output

{
  nIndexesWas: 2,
  ok: 1
}

The exact value of nIndexesWas depends on how many indexes existed before removing the compound index.

Verify the remaining indexes:

db.students.getIndexes()

Key Takeaways

  • A compound index contains two or more fields.
  • Use createIndex() to create a compound index.
  • The order of fields in a compound index is important.
  • Each field can have an ascending (1) or descending (-1) direction.
  • Compound indexes can support queries involving their indexed fields.
  • A compound index can generally support queries using its prefix fields.
  • An index { course: 1, age: 1 } is useful for queries beginning with course.
  • The same index is not generally suitable for queries that only search by age.
  • explain("executionStats") can help determine whether MongoDB uses a compound index.
  • getIndexes() displays existing indexes.
  • dropIndex() removes a specific compound index.
  • Creating many unnecessary indexes can increase storage usage and the work required when documents are inserted or modified.
  • Compound indexes should be designed around the application’s actual query and sorting patterns.

FAQs

1. What is a compound index in MongoDB?

A compound index is an index created using two or more fields.

db.students.createIndex({
  course: 1,
  age: 1
})

2. Why are compound indexes used in MongoDB?

Compound indexes are useful when an application frequently queries or sorts documents using multiple fields together.

3. Does the order of fields matter in a compound index?

Yes. The order is important because MongoDB uses the fields in the index according to their defined order.

4. Can a compound index be used for a query on its first field?

Yes. For example, an index on { course: 1, age: 1 } can generally support a query that searches by course.

5. Can a compound index on course and age efficiently support a query only on age?

Not generally. Because course is the first field in the index, a query using only age does not have the same index-prefix advantage. If age is frequently queried by itself, an index beginning with age may be more suitable.

6. How can you check whether MongoDB uses a compound index?

Use explain("executionStats").

db.students.find({
  course: "Python",
  age: { $gt: 15 }
}).explain("executionStats")

Look at the winning execution plan to see whether an index scan such as IXSCAN is used.

7. How do you delete a compound index in MongoDB?

Use dropIndex() with the compound index name.

db.students.dropIndex("course_1_age_1")

Written by Shubhranshu Shekhar, who has trained 20000+ students in coding.

Related Posts

Scroll to Top