MongoDB Covered Queries

In MongoDB, a covered query is a query in which all the fields are contained in the index. When all the fields of a query are contained in the index, MongoDB can directly retrieve the query results from the index without scanning the documents in the collection.

Basic Concepts

Structure of Index

MongoDB's index is a data structure that helps us quickly find documents. The structure of an index is usually a B-tree or B+ tree.

How Covered Queries Work

When we execute a covered query, MongoDB directly retrieves the query results from the index without scanning the documents in the collection. This can improve query performance because the size of the index is usually much smaller than the size of the collection.

Conditions for Covered Queries

To execute a covered query, the fields of the query must meet the following conditions:

1. All fields of the query must be contained in the index.
2. The fields of the query cannot contain array fields or nested document fields.
3. The fields of the query cannot contain geospatial fields.

Creating Covered Queries

Creating the Right Index

To execute a covered query, we need to create the right index. The index should contain all the fields of the query.

// Create an index containing name and age fields
db.users.createIndex({ name: 1, age: 1 })

// Create an index containing status and score fields
db.users.createIndex({ status: 1, score: 1 })

Executing Covered Queries

// Query documents where the name field starts with "J" and returns only name and age fields
db.users.find(
  { name: /^J/ },
  { name: 1, age: 1, _id: 0 }
)

// Query documents where the status field is "active" and returns only status and score fields
db.users.find(
  { status: "active" },
  { status: 1, score: 1, _id: 0 }
)

Verifying if a Query is a Covered Query

We can use the explain() method to verify if a query is a covered query.

// Verify if the query is a covered query
db.users.find(
  { name: /^J/ },
  { name: 1, age: 1, _id: 0 }
).explain()

In the returned query plan, we can check the stage field to determine if the query is a covered query. If the stage field is IXSCAN, it means the query is a covered query; if the stage field is FETCH, it means the query is not a covered query.

Optimizing Covered Queries

Choosing the Right Index

To execute a covered query, we need to choose the right index. The index should contain all the fields of the query.

Avoiding the _id Field

If we don't want to return the _id field, we can specify _id: 0 in the query. This can avoid MongoDB from scanning the documents in the collection.

Avoiding Array Fields or Nested Document Fields

If the fields of the query contain array fields or nested document fields, MongoDB cannot execute a covered query.

Summary

In MongoDB, a covered query is a query in which all the fields are contained in the index. When all the fields of a query are contained in the index, MongoDB can directly retrieve the query results from the index without scanning the documents in the collection. This can improve query performance. To execute a covered query, we need to create the right index and ensure that all fields of the query are contained in the index. At the same time, we also need to avoid using array fields or nested document fields to ensure that the query can execute a covered query.