Covered queries with indexes in MongoDB - Mini Project: Build & Apply

Covered Queries with Indexes in MongoDB

📖 Scenario: You are managing a small online bookstore database. You want to speed up queries that search for books by author and title without fetching the entire document from the database.

🎯 Goal: Create a MongoDB collection called books with sample data. Then create a covered query by building an index on author and title fields. Finally, write a query that uses this index to return only the author and title fields, ensuring the query is covered by the index.

📋 What You'll Learn

Create a books collection with 3 documents containing author, title, and year fields

Create a compound index on author and title

Write a query that finds books by a specific author and returns only author and title fields

Ensure the query is covered by the index (no document fetch needed)

💡 Why This Matters

🌍 Real World

Covered queries help speed up database reads by using indexes alone, which is important for fast search features in apps like online bookstores.

💼 Career

Understanding covered queries and indexes is essential for database administrators and backend developers to optimize query performance and reduce server load.

1

Create the books collection with sample documents

Create a MongoDB collection called books and insert these exact documents: { author: "George Orwell", title: "1984", year: 1949 }, { author: "Harper Lee", title: "To Kill a Mockingbird", year: 1960 }, and { author: "George Orwell", title: "Animal Farm", year: 1945 }.

MongoDB

// Insert the 3 specified documents into the books collection
// Your code here

Need a hint?

Use db.books.insertMany([...]) with the exact documents inside the array.

2

Create a compound index on author and title

Create a compound index on the books collection for the fields author and title in ascending order.

MongoDB

db.books.insertMany([
  { author: "George Orwell", title: "1984", year: 1949 },
  { author: "Harper Lee", title: "To Kill a Mockingbird", year: 1960 },
  { author: "George Orwell", title: "Animal Farm", year: 1945 }
])
// Create the compound index on author and title
// Your code here

Need a hint?

Use db.books.createIndex({ author: 1, title: 1 }) to create the index.

3

Write a query to find books by George Orwell returning only author and title

Write a MongoDB query on the books collection to find documents where author is "George Orwell". Return only the author and title fields (exclude _id).

MongoDB

db.books.insertMany([
  { author: "George Orwell", title: "1984", year: 1949 },
  { author: "Harper Lee", title: "To Kill a Mockingbird", year: 1960 },
  { author: "George Orwell", title: "Animal Farm", year: 1945 }
])
db.books.createIndex({ author: 1, title: 1 })
// Write the query to find George Orwell's books returning only author and title
// Your code here

Need a hint?

Use db.books.find({ author: "George Orwell" }, { author: 1, title: 1, _id: 0 }) to return only the needed fields.

4

Ensure the query is covered by the index

Add the .explain("executionStats") method to the query to check that the query is covered by the index (no document fetch).

MongoDB

db.books.insertMany([
  { author: "George Orwell", title: "1984", year: 1949 },
  { author: "Harper Lee", title: "To Kill a Mockingbird", year: 1960 },
  { author: "George Orwell", title: "Animal Farm", year: 1945 }
])
db.books.createIndex({ author: 1, title: 1 })
db.books.find(
  { author: "George Orwell" },
  { author: 1, title: 1, _id: 0 }
).explain("executionStats")
// Your code here

Need a hint?

Add .explain("executionStats") after the find() query to check the query plan.