You've probably heard that column indexing is a great way to optimize query performance by minimizing the number of disk accesses required by the query. MongoDB has a specific application of field indexing called Covered Queries, where all of a query's columns are indexed. Covered Queries are very fast because MongoDB doesn't have to examine any documents apart from the indexed ones. In today's blog, we'll be learning how to use Covered Queries to query data faster.

Covered Queries Defined

In the intro paragraph, we alluded to how all of a covered query's columns are indexed. There's slightly more to it than that. Specifically, a covered query is a query in which:

• All the fields in the query are part of an index.
• All the fields returned in the query are in the same index.

Behind the scenes, MongoDB matches the query conditions and returns the result using the same index without actually looking inside the documents. Since indexes are present in RAM, fetching data from indexes is much faster as compared to fetching data by scanning documents.

Now that we know exactly what constitutes a covered query, let's write some!

Creating the Indexes

We'll run our query against the film table of the Sakila Sample Database. It contains a number of fields pertaining to fictional movies. These include the title, a description, release year, as well as rental information such as the price and rental duration. Here's a document in Navicat for MongoDB's Tree View:

Let's create a compound index on the title and release_year fields:

• Click the large Index button on the main toolbar followed by the New Index button on the Objects toolbar:
  
  
  On the General tab,
  
  
• Select "film" from the Collection Name drop-down list.
• Under the "Index Version:" header, select "title" from the Field drop-down and choose "ASC" from the Type drop-down.
• Then, click the plus (+) button at the bottom of the screen to add a second field:
  
  
  
• Select "release_year" from the Field drop-down and once again choose "ASC" from the Type drop-down.
• Now, click on Text tab, and, under the "Weights" header, follow the same process as above to select the two fields from the Field drop-down and assign a weight of 1 for both fields:
  
  
  
  
• Finally, click the Save button, and give the index a name of "film_title_year".

Executing the Covered Query

To execute a query against our indexed fields:

• Click the large Query button on the main toolbar followed by the New Query button on the Objects toolbar.
• In the query editor, type the following find() invocation:
  
  db.film.find({title:{$regex : ".*AGENT.*"}},{title:1,release_year:1,_id:0})
  
  
• Click the Run button to execute the query. Here are the results:
  
  
  

You can choose Query > Explain from the main menu to see the execution stats on the query:

Conclusion

If you're looking to give your queries a boost, consider using Covered Queries. They are very fast because MongoDB only has to examine indexed documents, which are present in RAM.