When I do a $or query with sort(), the performance is poor.

[gen liu]

First, I do a query with sort():
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"
]
]
}
}

As you see, in the "$or query with sort()", it took 3800 ms, when the
normal query with sort() took 116ms.
Any advice?

[Tyler Brock]

Gen,

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 the
other two cases.

I would vote up the issue to prioritize it.

-Tyler

[gen liu]

Thanks a lot.

On 12月1日, 上午3时46分, Tyler Brock <ty...@10gen.com> wrote:
> Gen,
>
> 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 the
> other two cases.
>
> I would vote up the issue to prioritize it.
>
> -Tyler
>

[gen liu]

Hi,If I want using the hint() to specify more than one index,what
should I do?
Like:
db.test.find({"$or":[{"name":"jhon"},{"age":25}]}).sort({"sex":
1}).hint("?")
May be I have two indexes, one is {"name":1}, another is {"age":1},in
this case how could i use the hint()?

On 2011年12月1日, 上午3时46分, Tyler Brock <ty...@10gen.com> wrote:
> Gen,
>
> 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 the
> other two cases.
>
> I would vote up the issue to prioritize it.
>
> -Tyler
>

[Nat]

You can't. Only one index can be used at a time.

--
You received this message because you are subscribed to the Google Groups "mongodb-user" group.
To post to this group, send email to mongod...@googlegroups.com.
To unsubscribe from this group, send email to mongodb-user...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/mongodb-user?hl=en.

[gen liu]

Thanks