4

I have a users collection in MongoDB. I have defined the "status" key to be index like this:

db.users.createIndex("status": 1)

I am trying to find all of the users which their status is set to "Active".

I have total of 24 users in the DB. And 17 of them are active.

When doing the next command:

db.users.find("status": "Active").explain("executionStats")

I receive the next:

"executionStats" : {
        "executionSuccess" : true,
        "nReturned" : 17,
        "executionTimeMillis" : 2,
        "totalKeysExamined" : 17,
        "totalDocsExamined" : 17,
...

The totalDocsExamined is clear - Mongo was scanning 17 documents in total and skip the rest 7 (remember that we have total of 24 users in collection in total). That is awesome and that is very efficient - since I have asked to find and have the data of each document.

But when I am doing the same query, but now using the .count() method - I don't want Mongo to scan 17 records, I want him to scan 0 records and immediately give me the total count of results - without iterating on the whole DB - since when there billions of records this task can shuts the whole server down.

So I am doing this:

db.users.explain("executionStats").find("status": "Active").count()

I receive this:

"executionStats" : {
        "executionSuccess" : true,
        "nReturned" : 0,
        "executionTimeMillis" : 0,
        "totalKeysExamined" : 18,
        "totalDocsExamined" : 0,

As you can see: totalDocsExamined is 0 - this is great. But totalKeysExamined is 18!

Did Mongo iterates all over those 18 records to tell me that there 18 records? Does it efficient? If there were 2.5b of records in this collection with the same status? Since I don't need the data with count query, but only the count - is this query efficient?

And BTW - when removing the executionStats from the explain method, Mongo return 17 records to be precise. Only when using the executionStats it examined 18 keys - why there is extra key that Mongo examined?

Raz Buchnik
  • 7,753
  • 14
  • 53
  • 96
  • Yes slightly different naming in modern versions, but they mean the same things. Your `.count()` for example did not actually need to get content from any "document" and the "key" ( typically from index ) had all the data that was needed for the operation to complete. As for the "counts" themselves, it's some general weirdness in how indexes actually get processed and been around for a while. Generally though this is not a "programming question". If you want to discuss database internals further, then please ask on [dba.stackexchange.com](https://dba.stackexchange.com) instead. – Neil Lunn Nov 24 '18 at 08:44

0 Answers0