sorting, does not use defined index.

[greenman]

I have a collection 'thread' with following attributes(sender,
recipient,deleted_by_sender,deleted_by_recipient,added_at). I have two
compound indexes on this collections
1 => sender&deleted_by_sender&added_at
2 => recipient&deleted_by_recipient&added_at

a normal query like following, uses the index, which is nice,

Thread.db.collection('threads').find({'$or' =>[{ 'recipient'=>
user._id,:is_deleted_by_recipient => false},{'sender'=>
user._id, :is_deleted_by_sender => false, :is_replied =>
true}]}).explain

explain result ["nscanned"=>50, "nscannedObjects"=>50, "n"=>50,
"millis"=>19,]

but moment I add sorting to it, it goes back through the whole set of
records. Although the sorting attribute is part of the compound index.

Thread.db.collection('threads').find({'$or' =>[{ 'recipient'=>
user._id,:is_deleted_by_recipient => false},{'sender'=>
user._id, :is_deleted_by_sender => false, :is_replied => true}]},
{:sort => [["added_at",-1]]}).explain

explain result ["nscanned"=>545760, "nscannedObjects"=>545760,
"n"=>50, "millis"=>960]

do i need to support with a hint to the query, any ideas how the query
optimizer could be told to use an index. any idea would be great help..

[Marc]

Hello.

Can you please run both queries in the shell with .explain(true)?
This will provide more information about how Mongo is using the
indexes, and it will help us to diagnose your issue more accurately.

Thanks.

[greenman]

Hi Marc,
Thanks for your answer...
I tried to get the queries from log file while using mongodb ruby
driver. the unfortunate thing is even if I add the sort clause to the
query in log, its not shown as part of the query. .. I see both
queries with and without sort clause are same in log file ({:sort
=> [["added_at",-1]]})

hence after adding manually sort to the queries on mongo shell I got
the following plan.. which looks similar to the one described above...

1) Query without sort....

db.threads.find({"$or":
[{'recipient_id':ObjectId('4d0a429ad7226105c60006ac'),
'is_deleted_by_recipient':false}, {'is_replied':true,
'is_deleted_by_sender':false,
'sender_id':ObjectId('4d0a429ad7226105c60006ac')}]}).explain(true)
{
"clauses" : [
{
"cursor" : "BtreeCursor
recipient_id_-1_is_deleted_by_recipient_-1_message_added_at_-1",
"nscanned" : 50,
"nscannedObjects" : 50,
"n" : 50,
"millis" : 0,
"indexBounds" : {
"recipient_id" : [
[
ObjectId("4d0a429ad7226105c60006ac"),
ObjectId("4d0a429ad7226105c60006ac")
]
],
"is_deleted_by_recipient" : [
[
false,
false
]
],
"message_added_at" : [
[
{
"$maxElement" : 1
},
{
"$minElement" : 1
}
]
]
},
"allPlans" : [
{
"cursor" : "BtreeCursor
recipient_id_-1_is_deleted_by_recipient_-1_message_added_at_-1",
"indexBounds" : {
"recipient_id" : [
[
ObjectId("4d0a429ad7226105c60006ac"),
ObjectId("4d0a429ad7226105c60006ac")
]
],
"is_deleted_by_recipient" : [
[
false,
false
]
],
"message_added_at" : [
[
{
"$maxElement" : 1
},
{
"$minElement" : 1
}
]
]
}
}
]
},
{
"cursor" : "BtreeCursor
sender_id_-1_is_deleted_by_sender_-1_message_added_at_-1",
"nscanned" : 0,
"nscannedObjects" : 0,
"n" : 0,
"millis" : 0,
"indexBounds" : {
"sender_id" : [
[
ObjectId("4d0a429ad7226105c60006ac"),
ObjectId("4d0a429ad7226105c60006ac")
]
],
"is_deleted_by_sender" : [
[
false,
false
]
],
"message_added_at" : [
[
{
"$maxElement" : 1
},
{
"$minElement" : 1
}
]
]
},
"allPlans" : [
{
"cursor" : "BtreeCursor
sender_id_-1_is_deleted_by_sender_-1_message_added_at_-1",
"indexBounds" : {
"sender_id" : [
[
ObjectId("4d0a429ad7226105c60006ac"),
ObjectId("4d0a429ad7226105c60006ac")
]
],
"is_deleted_by_sender" : [
[
false,
false
]
],
"message_added_at" : [
[
{
"$maxElement" : 1
},
{
"$minElement" : 1
}
]
]
}
},
{
"cursor" : "BasicCursor",
"indexBounds" : {

}
}
]
}
],
"nscanned" : 50,
"nscanneObjects" : 50,
"n" : 50,
"millis" : 0
}

2) Query with sort option....
db.threads.find({"$or":
[{'recipient_id':ObjectId('4d0a429ad7226105c60006ac'),
'is_deleted_by_recipient':false}, {'is_replied':true,
'is_deleted_by_sender':false,
'sender_id':ObjectId('4d0a429ad7226105c60006ac')}]}).sort({message_added_at:
-1}).explain(true);
{
"cursor" : "BtreeCursor message_added_at_-1",
"nscanned" : 545760,
"nscannedObjects" : 545760,
"n" : 50,
"millis" : 925,
"indexBounds" : {
"message_added_at" : [
[
{
"$maxElement" : 1
},
{
"$minElement" : 1
}
]
]
},
"allPlans" : [
{
"cursor" : "BtreeCursor message_added_at_-1",
"indexBounds" : {
"message_added_at" : [
[
{
"$maxElement" : 1
},
{
"$minElement" : 1
}
]
]
}
}
],
"oldPlan" : {
"cursor" : "BtreeCursor message_added_at_-1",
"indexBounds" : {
"message_added_at" : [
[
{
"$maxElement" : 1
},
{
"$minElement" : 1
}
]
]
}
}
}

Perhaps you have clue what could be wrong..

thxs,,

[Marc]

Thank you for sending this output.  I have been able to recreate this,
and this is actually a known issue:
https://jira.mongodb.org/browse/SERVER-1205
The fix version is 2.1
Unfortunately this does not immediately help your situation.  Perhaps
a possible work around could be doing the query and sort in two
operations, either doing the sort in your application, or saving the
results of the query to a temporary collection and sorting that.  In
the Jira case, another user left a comment with their work-around.
 These are obviously imperfect solutions.  Please vote for this issue.