CODEWITHSUNDEEP
mongodb
Johny T Koshy
Johny T Koshy

Reputation: 3912

Why there are so many nYields for this simple MongoDB query?

I have a test db with following indexes: 

[
    {
            "v" : 1,
            "key" : {
                    "_id" : 1
            },
            "name" : "_id_",
            "ns" : "blogger.users"
    },
    {
            "v" : 1,
            "key" : {
                    "name" : 1,
                    "age" : 1
            },
            "name" : "name_1_age_1",
            "ns" : "blogger.users"
    },
    {
            "v" : 1,
            "key" : {
                    "age" : 1,
                    "name" : 1
            },
            "name" : "age_1_name_1",
            "ns" : "blogger.users"
    }
]

When running the following query: 

> db.users.find({"age":{"$gte":21,"$lte":30}})
     .sort({"name":1})
     .hint({"name":1,"age":1})
     .explain()

I get the following results

{
    "cursor" : "BtreeCursor name_1_age_1",
    "isMultiKey" : false,
    "n" : 83116,
    "nscannedObjects" : 1000000,
    "nscanned" : 1000000,
    "nscannedObjectsAllPlans" : 1000000,
    "nscannedAllPlans" : 1000000,
    "scanAndOrder" : false,
    "indexOnly" : false,
    "nYields" : 7812,
    "nChunkSkips" : 0,
    "millis" : 1926,
    "indexBounds" : {
            "name" : [
                    [
                            {
                                    "$minElement" : 1
                            },
                            {
                                    "$maxElement" : 1
                            }
                    ]
            ],
            "age" : [
                    [
                            {
                                    "$minElement" : 1
                            },
                            {
                                    "$maxElement" : 1
                            }
                    ]
            ]
    },
    "server" : "Johny-PC:27017",
    "filterSet" : false
}

I ran this query multiple times and nYields are always above 7k. There arent any other processes updating, inserting or deleting data. When I run the same query without hint the result is the same.

If I run same query using the other index as hint then nYields are 1200+.

Without hint and sort it is fast but with nYields:500+ and uses another index, as expected.

So is there a way to find out why these many nYields are there and can someone explain why is it so?

EDIT: A simple find query also has that many nYields. So, the problem is not with index. I might be missing something.

Upvotes: 2

Views: 323

Answers (1)

JohnnyHK
JohnnyHK

Reputation: 312045

You're forcing the query to use the wrong index with the hint call, as the query needs to first filter by age and then sort by name. The index you're telling MongoDB to use can't be efficiently used for that.

Either remove the hint call or change it to:

db.users.find({"age":{"$gte":21,"$lte":30}})
 .sort({"name":1})
 .hint("age_1_name_1")
 .explain()

The more docs the query needs to scan (nscannedObjects) the more times it will yield the read lock as it's waiting for the I/O to complete to fetch the docs. So the less efficient the query, the higher nYields will be. There's nothing wrong with 500+ yields, it just means it's a query that returns a lot of docs (83k in this case).

Upvotes: 3

Related Questions