CODEWITHSUNDEEP
mongodbmongodb-query
StefanMK
StefanMK

Reputation: 1303

Why is MongoDB using scanAndOrder here?

I am saving games documents in MongoDB. Among other things the documents contain the name of the player (name), the time when the game has ended (endMS) and the type of the game (type). Type can have one out of five different values.

I need to search for all finished games by a player sorted by the time when the game has ended and for all finished games by a player with a certain game type also sorted by the time of the game end.

Examples for both queries are

db.games.find({name:"Stefan",endMS:{$gt:0}}).sort({endMS:-1})

and

db.games.find({name:"Stefan",type:"bli",endMS:{$gt:0}}).sort({endMS:-1})

You can use the indexes

db.games.ensureIndex({name:1,endMS:-1})

and

db.games.ensureIndex({name:1,type:1,endMS:-1})

for fast access.

Now I am trying to get along with just one index:

db.games.ensureIndex({name:1,endMS:-1,type:1})

The first query or course still runs fine. The idea for the second query is that Mongo might need to skip some entries when scanning the index but only need to access the documents that are finally returned by the query because the "type" can already be checked in the index. That should be fast enough for my needs.

However using explain() MongoDB tells me that "scanAndOrder" is needed when querying the database like this.

db.games.find({name:"Stefan",type:"bli",endMS:{$gt:0}}).sort({endMS:-1}).explain()

{
    "cursor" : "BtreeCursor name_1_endMS_-1_type_1",
    "isMultiKey" : false,
    "n" : 1,
    "nscannedObjects" : 1,
    "nscanned" : 22,
    "nscannedObjectsAllPlans" : 4,
    "nscannedAllPlans" : 25,
    "scanAndOrder" : true,
    "indexOnly" : false,
    "nYields" : 0,
    "nChunkSkips" : 0,
    "millis" : 0,
    "indexBounds" : {
        "name" : [
            [
                "Stefan",
                "Stefan"
            ]
        ],
        "endMS" : [
            [
                Infinity,
                0
            ]
        ],
        "type" : [
            [
                "bli",
                "bli"
            ]
        ]
    },
    "server" : "localhost:27017",
    "filterSet" : false
}

nscannedObjects and nscanned are as expected like described above, but I am wondering why Mongo says scanAndOrder:true.

According to the docs: "scanAndOrder is a boolean that is true when the query cannot use the order of documents in the index for returning sorted results: MongoDB must sort the documents after it receives the documents from a cursor."

As far as I have understood it the documents should be ordered in the index, only some need to be skipped which doesn't affect the order.

So why is MongoDB using scanAndOrder here?

Upvotes: 3

Views: 2172

Answers (3)

Dan P
Dan P

Reputation: 111

Stefan reported the issue here: http://jira.mongodb.org/browse/SERVER-12935 and the scanAndOrder issue was resolved after 2.6.0-rc0, but there are some lingering issues with the explain output.

Upvotes: 0

StefanMK
StefanMK

Reputation: 1303

This seems to be a bug in MongoDB 2.6.0-rc0. All works as expected in MongoDB 2.4.9.

Upvotes: 3

user2833162
user2833162

Reputation: 149

http://docs.mongodb.org/manual/reference/glossary/

natural order: The order that a database stores documents on disk. Typically, the order of documents on disks reflects insertion order, except when a document moves internally because an update operation increases its size. In capped collections, insertion order and natural order are identical because documents do not move internally. MongoDB returns documents in forward natural order for a find() query with no parameters. MongoDB returns documents in reverse natural order for a find() query sorted with a parameter of $natural:-1. See $natural.

Your assumption of insertion order == indexing order is wrong.

Upvotes: -2

Related Questions