Skip to content
Studio 3T - The professional GUI, IDE and client for MongoDB
  • Tools
    • Aggregation Editor
    • IntelliShell
    • Visual Query Builder
    • Export Wizard
    • Import Wizard
    • Query Code
    • SQL Query
    • Connect
    • Schema Explorer
    • Compare
    • SQL ⇔ MongoDB Migration
    • Data Masking
    • Task Scheduler
    • Reschema
    • More Tools and Features
  • Solutions
  • Resources
    • Knowledge Base
    • MongoDB Tutorials & Courses
    • Tool/Feature Documentation
    • Reports
    • Case Studies
    • Whitepapers
    • Blog
    • Testimonials
    • Community
  • Contact us
    • Contact
    • Sales Support
    • Feedback and Support
    • Career
    • About Us
  • Store
    • Buy Now
    • Preferred Resellers
    • Team Pricing
  • My License
  • Download
search

What’s a covered query in MongoDB? #Studio3T_AMA

Posted on: 27/04/2022 by DJ Walker-Morgan

Q: I’ve heard that covered queries in MongoDB perform much faster. But what are they and how can I use Studio 3T to find out if my query is covered?

Welcome to the latest Studio 3T Ask Manatees Anything – #Studio3T_AMA. Want to know how to use Studio 3T to make your MongoDB life better? Just tweet and use #Studio3T_AMA or email [email protected].

A: Let’s start with what a covered query is. When you create an index in MongoDB, you can have multiple different fields contributing keys to an index. When you perform a query, the query engine will work out the best way to run the query. The system looks to see if all the data required to produce results is available in the index. If it is, then that query is covered (by the index). When it’s covered, the system only retrieves data from the index which should already be in memory. That’s the theory at least. Let’s break out the Studio 3T and try it ourselves.

Practically Covering Queries

So, let’s imagine you have a collection with department, username and logincount… along with lots more user data. For this example, let’s say you regularly search on department and username, and maybe regularly sort by descending login_count. Let’s index that by department and username and then do a query on the department. It comes back fast enough. And if we look at the Explain tab in Studio 3T we can see where that index plays a part:

But for every result found by the index scan, at the start each document then has to be retrieved. Here, that’s 45,000 documents. They are then filtered down in the projection stage. 87.3MiB of data retrieved according to the Explain stats. 

All the data is in our index however, so you’d think this would be a covered query. There’s one more condition to making a covered query. And that’s the slightly counter-intuitive one. You need to exclude the _id field too because that is included by default. If it is included by default, the query engine pulls the documents from the database. That lets it make a complete document for the rest of the query to process.

So, let’s exclude the _id field from the results (adding { "id":0 } to the projection). Now when we re-run our query and look at the Explain view:

Now we can see the PROJECTION_COVERED stage that sees us go from scanning our index straight to the results. That’s how Studio 3T makes it easier to see if your query is covered.

Covering more with a Covered Query

Covered queries are fragile things though. If we were to start retrieving and sorting on the logincount fields, things would change. Let’s add it to the projection and set it in the sort options. Now, looking at the Explain tab shows us that we are back to retrieving the whole documents:

We can add the logincount field to our index. Studio 3T makes that simple by letting you add fields to the index definition. When you update the index, Studio 3T then recreates the index (as MongoDB doesn’t allow for modifying indexes). 

Pro-tip : remember to rename your index if you are adding fields to remind yourself what the index actually contains.

If we go back and rerun the query then check the Explain view we can see this works:

There’s the PROJECTION_COVERED stage showing we covered the query by expanding the index.

Caveat Indexer

This can make a huge difference to performance, because, in most circumstances, you’ll have the index in memory. If your indexes can’t fit in memory, you’ll usually have performance issues. But that’s outside the scope of this particular AMA (Ask Manatees Anything).

Always remember to make your indexes fit the queries you are doing. Unused indexes cost performance at write time and are a RAM usage overhead. Index memory will fight with your working set memory as it tries to make best use of your RAM.

As long as your index is in memory and your query is covered, the results will be coming straight from RAM. And that’s a great way to speed up things.


How helpful was this article?
This article was hideous
This article was bad
This article was ok
This article was good
This article was great
Thank you for your feedback!

About The Author

DJ Walker-Morgan

Dj has been around since Commodore had Pets and Apples grew everywhere. With a background in Unix and development, he's been around the technology business writing code or writing content ever since.

Related articles

  • How does Studio 3T’s SQL Query work? #Studio3T_AMA
  • Is there a quicker way to query _id values? #Studio3T_AMA
  • What is Query Assist in Studio 3T? #Studio3T_AMA
  • Help! How can I find my lost query! #Studio3T_AMA
  • What’s a default query? #Studio3T_AMA

Tags

academy aggregation AMA atlas community connections date tags export features hackolade import intellishell JSON knowledge base migration modelling mongodb mongoodb productivity query regex releases schema security SQL Studio 3T tasks tips updates webinar windows

Browse by MongoDB topic

  • Connecting to MongoDB
  • Database Administration & Security
  • Getting Started with MongoDB
  • Getting Started with Studio 3T
  • Import/Export
  • Job Automation & Scheduling
  • MongoDB Aggregation Framework
  • MongoDB/Studio 3T Workshops
  • Performance
  • Query & CRUD Operations
  • Schema
  • Studio 3T Licensing
  • Support and other resources
  • Working with MongoDB & SQL
  • Working with MongoDB Atlas

Studio 3T

MongoDB Enterprise Certified Technology PartnerSince 2014, 3T has been helping thousands of MongoDB developers and administrators with their everyday jobs by providing the finest MongoDB tools on the market. We guarantee the best compatibility with current and legacy releases of MongoDB, continue to deliver new features with every new software release, and provide high quality support.

Find us on FacebookFind us on TwitterFind us on YouTubeFind us on LinkedIn

Education

  • Free MongoDB Tutorials
  • Connect to MongoDB
  • Connect to MongoDB Atlas
  • Import Data to MongoDB
  • Export MongoDB Data
  • Build Aggregation Queries
  • Query MongoDB with SQL
  • Migrate from SQL to MongoDB

Resources

  • Feedback and Support
  • Sales Support
  • Knowledge Base
  • FAQ
  • Reports
  • Case Studies
  • White Papers
  • Testimonials
  • Discounts

Company

  • About Us
  • Blog
  • Careers
  • Legal
  • Press
  • Privacy Policy
  • EULA

© 2022 3T Software Labs GmbH. All rights reserved.

  • Privacy Policy
  • Cookie settings
  • Impressum
When you click "Accept", you are agreeing to cookies being on your device. They may improve site navigation, site usage analysis, or the relevance of messages. It is up to you which cookies are enabled. Read our Privacy Policy.
Manage cookies
Accept
✕

Privacy Preference Center

A cookie is a small file of letters and numbers that is downloaded on to your computer when you visit a website. Cookies are used by many websites and can do a number of things, eg remembering your preferences, recording what you have put in your shopping basket, and counting the number of people looking at a website. In the form below you can choose which cookies, except for essential cookies, to allow or disable.

Accept all cookies

Manage consent preferences

Essential cookies are strictly necessary to provide an online service such as our website or a service on our website which you have requested. The website or service will not work without them.

Performance cookies allow us to collect information such as number of visits and sources of traffic. This information is used in aggregate form to help us understand how our websites are being used, allowing us to improve both our website’s performance and your experience.

Functional cookies collect information about your preferences and choices and make using the website a lot easier and more relevant. Without these cookies, some of the site functionality may not work as intended.

Social media cookies are cookies used to share user behaviour information with a third-party social media platform. They may consequently effect how social media sites present you with information in the future.

Accept selected