MongoDB $query operator ignores index?

Question

I created a test MongoDB collection "sampleCollection" with documents which looks like:

 "_id" : ObjectId("510929e041cb2179b41ace1c"),
 "stringField" : "Random string0",
 "longField" : NumberLong(886)

and has index on field "stringField". When I execute

db.sampleCollection.find({"stringField":"Random string0"}).explain()

everything is ok:

 "cursor" : "BtreeCursor stringField_1",
 "isMultiKey" : false,
 "n" : 2,
 "nscannedObjects" : 2,
 "nscanned" : 2,
 "nscannedObjectsAllPlans" : 2,
 "nscannedAllPlans" : 2,
 "scanAndOrder" : false,
 "indexOnly" : false,
 "nYields" : 0,
 "nChunkSkips" : 0,
 "millis" : 0,
 "indexBounds" : {
         "stringField" : [
                 [
                         "Random string0",
                         "Random string0"
                 ]
         ]
 }

but

db.sampleCollection.find({$query:{"stringField":"Random string0"}}).explain()

gets me

"cursor" : "BasicCursor",
"isMultiKey" : false,
"n" : 0,
"nscannedObjects" : 4,
"nscanned" : 4,
"nscannedObjectsAllPlans" : 4,
"nscannedAllPlans" : 4,
"scanAndOrder" : false,
"indexOnly" : false,
"nYields" : 0,
"nChunkSkips" : 0,
"millis" : 0,
"indexBounds" : {

}

This is not looks like a problem, but I'm using org.springframework.data.mongodb framework in production and usage of query constructions is an only way to write repositories. And thus I have a db which completely ignores indexed data.

Is it correct? Or I misunderstood something?

I saw this exact problem with `$sort` on another question there was no real solution to it :( yea not much help but lets you know this probably isn't just something noobish — Sammaye, Jan 30 '13 at 17:22
I tried to search the [MongoDB jira](https://jira.mongodb.org/secure/IssueNavigator.jspa) for problems with `$query` but I can't figure out how to search for that w/o getting the thousands of results for `query` as well. Anyone know how? — JohnnyHK, Jan 30 '13 at 17:40
Even more funny when you use hint for an index it not even returns results. — attish, Jan 30 '13 at 17:44

score 5 · Accepted Answer · answered Jan 30 '13 at 20:49

That was funny i cannot decide to say it is a bug or not it is up to you:

There are two available syntax: http://docs.mongodb.org/manual/reference/operator/query/

When you using:

db.collection.find( { age : 25 } )

also will

db.collection.find( { age : 25 } ).explain()
db.collection.find( { age : 25 } ).hint(someindex)

work fine.

When you using your solution (the other syntax):

db.collection.find( { $query: { age : 25 } } )

the output of

db.sampleCollection.find({$query:{"stringField":"Random string0"}}).explain()

Will show like the query not using the index

if you also use .hint for the index it will omit the result. :) (That is i do not really understand)

Fortunately there is another syntax for these operations too: you can use:

db.sampleCollection.find({$query:{"stringField":"Random string0"}, $explain:1})

it will have the right output and showed for me the usage of the index. Also there is similar syntax for $hint.

You can check the documentation here: http://docs.mongodb.org/manual/reference/meta-query-operators/

I found this really interesting so i turned on the profiler:

i made a test collection (queryTst) with around 250k docs each with only _id and an age field in the structure with an index on age.

For this query:

db.queryTst.find({$query:{"age":16},$explain:1})

i got:

{
    "cursor" : "BtreeCursor age_1",
    "isMultiKey" : false,
    "n" : 2,
    "nscannedObjects" : 2,
    "nscanned" : 2,
    "nscannedObjectsAllPlans" : 2,
    "nscannedAllPlans" : 2,
    "scanAndOrder" : false,
    "indexOnly" : false,
    "nYields" : 0,
    "nChunkSkips" : 0,
    "millis" : 0,
    "indexBounds" : {
        "age" : [
            [
                16,
                16
            ]
        ]
    },
    "allPlans" : [
        {
            "cursor" : "BtreeCursor age_1",
            "n" : 2,
            "nscannedObjects" : 2,
            "nscanned" : 2,
            "indexBounds" : {
                "age" : [
                    [
                        16,
                        16
                    ]
                ]
            }
        }
    ],
    "oldPlan" : {
        "cursor" : "BtreeCursor age_1",
        "indexBounds" : {
            "age" : [
                [
                    16,
                    16
                ]
            ]
        }
    },
    "server" : ""
}

for this:

 db.queryTst.find({$query:{"age":16},$explain:1}).explain()

i got:

"cursor" : "BasicCursor",
    "isMultiKey" : false,
    "n" : 0,
    "nscannedObjects" : 250011,
    "nscanned" : 250011,
    "nscannedObjectsAllPlans" : 250011,
    "nscannedAllPlans" : 250011,
    "scanAndOrder" : false,
    "indexOnly" : false,
    "nYields" : 0,
    "nChunkSkips" : 0,
    "millis" : 103,
    "indexBounds" : {

    },

in the profiler log: for the first

{
    "ts" : ISODate("2013-01-30T20:35:40.526Z"),
    "op" : "query",
    "ns" : "test.queryTst",
    "query" : {
        "$query" : {
            "age" : 16
        },
        "$explain" : 1
    },
    "ntoreturn" : 0,
    "ntoskip" : 0,
    "nscanned" : 2,
    "keyUpdates" : 0,
    "numYield" : 0,
    "lockStats" : {
        "timeLockedMicros" : {
            "r" : NumberLong(368),
            "w" : NumberLong(0)
        },
        "timeAcquiringMicros" : {
            "r" : NumberLong(8),
            "w" : NumberLong(5)
        }
    },
    "nreturned" : 1,
    "responseLength" : 567,
    "millis" : 0,
    "client" : "127.0.0.1",
    "user" : ""
}

for the second:

{
    "ts" : ISODate("2013-01-30T20:35:47.715Z"),
    "op" : "query",
    "ns" : "test.queryTst",
    "query" : {
        "query" : {
            "$query" : {
                "age" : 16
            },
            "$explain" : 1
        },
        "$explain" : true
    },
    "ntoreturn" : 0,
    "ntoskip" : 0,
    "nscanned" : 250011,
    "keyUpdates" : 0,
    "numYield" : 0,
    "lockStats" : {
        "timeLockedMicros" : {
            "r" : NumberLong(104092),
            "w" : NumberLong(0)
        },
        "timeAcquiringMicros" : {
            "r" : NumberLong(13),
            "w" : NumberLong(5)
        }
    },
    "nreturned" : 1,
    "responseLength" : 373,
    "millis" : 104,
    "client" : "127.0.0.1",
    "user" : ""
}

which somehow means to me that is the explain() cause the table scan in the mixed syntax.

There are two syntaxes and did not made for using mixed (both made to have same functionality? As i could see the docs , yes), they are not seem to be cross compatible. — attish, Jan 30 '13 at 22:19
The two are analogous of each other, as it says in the docs. But as to them being cross compatible; I am still unsure what you mean there. — Sammaye, Jan 30 '13 at 22:21
If you run only one syntax or only the other there is no problem. So to answer this question there is no problem with org.springframework.data.mongodb the problem related to mixing the two syntaxes and not just with explain. Also with all other cases. — attish, Jan 30 '13 at 22:28
Oh ok I misread a piece of your answer, I didn't see the middle query you did — Sammaye, Jan 30 '13 at 22:40
What do you think should i edit the text somehow to make it more readable? it is too long. — attish, Jan 30 '13 at 22:42
Everything is clear, thank you! I just was scared about read test in spring framework - it returns indexed results in 2000ms, but i forgot, that it takes much time to convert 10k of documents to Java classes. — FakeUser, Jan 31 '13 at 07:31

score 4 · Answer 2 · answered May 02 '13 at 00:20

this is not a bug, I happened to have the same question you had, and was about to report it as an issue in mongodb, but reading the documentation it specifies that you shouldn't mix cursor methods ( like the explain() ) with this query format, you should use $explain, like in the example provided in this page:

http://docs.mongodb.org/manual/reference/operator/query/

MongoDB $query operator ignores index?

2 Answers2

Linked