Mongo — Covered Query

An approach to optimize MongoDB queries

Why Covered Query?

Creating a Covered query is a way to optimize our Mongo DB read queries

What is Covered Query?

MongoDB able to provide response the query from an index without having to look through the documents

A covered query is a query that can be satisfied entirely using an index and does not have to examine any documents. — Query Optimization, Mongo Docs

How to create a Covered Query?

We will have to form our intended query for refactoring to have

  • all fields in query selector are part of an index
  • and no field in query are equal to null
  • all fields returned in response are in the same index

Usecase —

Let say we have an use-case to find list restaurants based on cuisine and here am using the Mongo sample restaurant database

The following query should filter and provide us a list of restaurants from our collection

Query to get list of restaurants

Benchmark

Pre-Index

> db.restaurants.find({‘cuisine’:’American’}).explain(“executionStats”);

Execution stats of the query
Execution Query of the query before making it a covered query

Creating Index:

Creating Index

Post Index:

Covered Query:

Now that we are already observing some improvements after creating index, we could change our query bit to make it a covered query

Covered Query

The winningPlan stage value in explain() of the query could be used to verify our query is covered by index and is a covered query

Pre Index vs With Index vs Covered Query

From the comparison table below could see our query optimized and there is significant drop of query execution time 50+%

Comparing Pre Index -With Index — Covered Query

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Balamurugan Mathivanan

Balamurugan Mathivanan

4 Followers

MEAN stack developer by profession and a Machine Learning enthusiast