Covered Query

The Concept of Covered Queries

A covered query refers to a query operation that can be fully satisfied by an index without needing to access the actual document data. This type of query is highly efficient because MongoDB only needs to scan the index without loading the documents themselves. A covered query occurs when the index contains all the fields required by the query.

// Example collection
db.users.insertMany([
  { _id: 1, name: "Alice", age: 25, email: "alice@example.com" },
  { _id: 2, name: "Bob", age: 30, email: "bob@example.com" },
  { _id: 3, name: "Charlie", age: 35, email: "charlie@example.com" }
]);

// Create a compound index
db.users.createIndex({ name: 1, age: 1 });

How Covered Queries Work

When executing a query, MongoDB first checks if a suitable index exists. A covered query is formed if the index includes all of the following:

1. All fields used in the query conditions
2. All fields required in the returned results
3. All fields used in sorting operations

// Example of a covered query
db.users.find(
  { name: "Alice", age: 25 },  // Query conditions
  { _id: 0, name: 1, age: 1 }  // Projection, only returning fields included in the index
).explain("executionStats");

Identifying Covered Queries

You can use the explain() method to confirm whether a query is covered by an index. In the output, the following fields indicate a covered query:

• totalDocsExamined: 0: Indicates no documents were examined
• indexOnly: true: Indicates the query used only the index

// Check if a query is covered
const explainResult = db.users.find(
  { name: "Alice" },
  { _id: 0, name: 1, age: 1 }
).explain("executionStats");

console.log(explainResult.executionStats.totalDocsExamined === 0);  // true
console.log(explainResult.executionStats.indexOnly);  // true

Advantages of Covered Queries

1. Performance Improvement: No need to read full documents from disk, reducing I/O operations
2. Memory Efficiency: Only index data needs to be processed in memory
3. Reduced CPU Usage: Avoids the overhead of document parsing
4. Query Speed: Typically an order of magnitude faster than regular queries

// Performance comparison test
const start1 = new Date();
for (let i = 0; i < 10000; i++) {
  db.users.find({ name: "Alice" }, { name: 1, age: 1, _id: 0 });
}
const duration1 = new Date() - start1;

const start2 = new Date();
for (let i = 0; i < 10000; i++) {
  db.users.find({ name: "Alice" });
}
const duration2 = new Date() - start2;

console.log(`Covered query duration: ${duration1}ms`);
console.log(`Regular query duration: ${duration2}ms`);

Conditions for Implementing Covered Queries

1. Projection Restrictions: Can only include fields present in the index
2. Excluding _id Field: Unless the _id field is part of the index
3. Field Order: The order of fields in a compound index affects coverage capability
4. Array Fields: Indexes containing array fields cannot fully cover queries

// Cases where a covered query is not possible
db.users.find(
  { name: "Alice" },
  { name: 1, email: 1 }  // email is not in the index
).explain("executionStats");

// Including the _id field also breaks the covered query
db.users.find(
  { name: "Alice" },
  { name: 1, age: 1 }  // _id is included by default
).explain("executionStats");

Compound Indexes and Covered Queries

The design of compound indexes directly impacts the possibility of covered queries. A well-designed index can support more types of covered queries.

// Example of a compound index
db.products.createIndex({ category: 1, price: 1, stock: 1 });

// These queries can be covered
db.products.find(
  { category: "Electronics", price: { $gt: 500 } },
  { category: 1, price: 1, _id: 0 }
);

db.products.find(
  { category: "Electronics" },
  { category: 1, stock: 1, _id: 0 }
).sort({ stock: -1 });

Limitations of Covered Queries

1. Geospatial Indexes: Cannot fully cover geospatial queries
2. Text Indexes: Do not support covered queries
3. Hash Indexes: Can only cover equality queries
4. Multikey Indexes: Queries on array fields may not be fully covered
5. Sparse Indexes: May not cover queries for all documents

// Geospatial indexes cannot fully cover
db.places.createIndex({ location: "2dsphere" });
db.places.find(
  { location: { $near: { $geometry: { type: "Point", coordinates: [ -73.9667, 40.78 ] } } } },
  { _id: 0, location: 1 }
).explain("executionStats");  // Will not be a fully covered query

Practical Application Scenarios

1. Report Generation: Only requires aggregated data from partial fields
2. Fast Counting: count() operations can use index coverage
3. Existence Checks: Only needs to know if a document exists without its content
4. API Responses: Returns minimal data structures

// Example of API response optimization
// Regular query
app.get('/api/users/minimal', (req, res) => {
  const users = db.users.find({}, { _id: 0, name: 1, avatar: 1 }).toArray();
  res.json(users);
});

// Optimized using covered queries
app.get('/api/users/optimized', (req, res) => {
  // Assuming a compound index { name: 1, avatar: 1 } exists
  const users = db.users.find({}, { _id: 0, name: 1, avatar: 1 }).toArray();
  res.json(users);
});

Monitoring and Optimizing Covered Queries

MongoDB's monitoring tools can help identify potential opportunities for optimizing covered queries:

// View query execution statistics
db.setProfilingLevel(2);  // Enable detailed profiling

// Execute some queries...

// Analyze slow queries
db.system.profile.find().sort({ millis: -1 }).limit(5).pretty();

// Find queries that can be optimized
db.system.profile.find({
  "query.$explain.indexOnly": false,
  "query.$explain.totalDocsExamined": { $gt: 0 }
}).pretty();

Impact of Index Selection on Covered Queries

MongoDB's query optimizer selects the most efficient index based on query patterns. Sometimes, forcing a specific index is necessary to achieve a covered query:

// Force a specific index
db.users.find(
  { name: "Alice", age: { $gt: 20 } },
  { _id: 0, name: 1, age: 1 }
).hint({ name: 1, age: 1 });  // Force the use of a compound index

// View available indexes
db.users.getIndexes();

Covered Queries and Aggregation Pipelines

The principles of covered queries can also be applied to optimize performance in aggregation pipelines:

// Covered query optimization in aggregation pipelines
db.orders.aggregate([
  { $match: { status: "completed", date: { $gte: new Date("2023-01-01") } } },
  { $project: { _id: 0, status: 1, date: 1, amount: 1 } },
  { $sort: { date: -1 } }
]);

// Ensure an index like { status: 1, date: 1 } or a compound index including these fields exists