When $ or and sort () are used in MongoDB, the query performance may be poor.

First, let's take a look at the operation process:

mongos> db.find({ "user" : "jhon"}).sort({"name" : 
1}).limit(100).explain() 
{ 
        "cursor" : "BtreeCursor user_1", 
        "nscanned" : 10100, 
        "nscannedObjects" : 10100, 
        "n" : 100, 
        "scanAndOrder" : true, 
        "millis" : 116, 
        "nYields" : 0, 
        "nChunkSkips" : 0, 
        "isMultiKey" : false, 
        "indexOnly" : false, 
        "indexBounds" : { 
                "user" : [ 
                        [ 
                                "jhon", 
                                "jhon" 
                        ] 
                ] 
        } 
} 

Second, I do $or query with sort(): 
mongos> db.find({ "$or" : [ { "user" : "jhon"} , { "owner" : 
"jhon"}]}).sort({"name" : 1}).limit(100).explain() 
{ 
        "cursor" : "BtreeCursor name_1", 
        "nscanned" : 1010090, 
        "nscannedObjects" : 1010090, 
        "n" : 100, 
        "millis" : 3800, 
        "nYields" : 0, 
        "nChunkSkips" : 0, 
        "isMultiKey" : false, 
        "indexOnly" : false, 
        "indexBounds" : { 
                "name" : [ 
                        [ 
                                { 
                                        "$minElement" : 1 
                                }, 
                                { 
                                        "$maxElement" : 1 
                                } 
                        ] 
                ] 
        } 
} 

Last, I do $or query without sort(): 
mongos> db.find({ "$or" : [ { "user" : "jhon"} , { "owner" : 
"jhon"}]}).limit(100).explain() 
{ 
        "cursor" : "BtreeCursor user_1", 
        "nscanned" : 100, 
        "nscannedObjects" : 100, 
        "n" : 100, 
        "millis" : 0, 
        "nYields" : 0, 
        "nChunkSkips" : 0, 
        "isMultiKey" : false, 
        "indexOnly" : false, 
        "indexBounds" : { 
                "user" : [ 
                        [ 
                                "jhon", 
                                "jhon" 
                        ] 
                ] 
        } 
} 

We can see that:

In the first query, sort () alone performs well.

In the second query, when $ or and sort () are used together, the performance is poor.

In the third query, $ or is used separately, and the performance is good.

Later in the official forum to ask a question, learned that there is a bug: https://jira.mongodb.org/browse/SERVER-1205

It will be modified in the future. Record it today.

Forum reply:

I believe the issue you are running into is expressed in this JIRA
Ticket: https://jira.mongodb.org/browse/SERVER-1205

I believe the query optimizer is choosing to use the name index and
Walk it backwards. As it goes through the index it compares the user
And owner attributes to your parameters and collects them in sorted
Order. As a result the nscanned objects is much larger than in
Other two cases.

I wocould vote up the issue to prioritize it.

-Taylor

 

Original post address: http://groups.google.com/group/mongodb-user/browse_thread/thread/58428702d9485b8/40d6db4604a95a69? Lnk = gst & q = gen + liu + % 24or #40d6db4604a95a69

 

NOTE: If $ or is used in the query and the query element has an index, the index of this element will also be used. In other words, if the query only has $ or (No sort), this problem will not occur. In addition, a similar problem is also found when the combination of $ and $ or is used.