Why is the first slower than the second. Both use the same index. These differences are consistent
db.tester.find({i: 9, j: {$lt: 10}}).sort({k: -1}).explain()
{
"cursor" : "BtreeCursor i_1_j_1_k_-1",
"isMultiKey" : false,
"n" : 10000,
"nscannedObjects" : 40000,
"nscanned" : 40000,
"scanAndOrder" : true,
"indexOnly" : false,
"nYields" : 0,
"nChunkSkips" : 0,
"millis" : 138,
"indexBounds" : {
"i" : [
[
9,
9
]
],
"j" : [
[
-1.7976931348623157e+308,
10
]
],
"k" : [
[
{
"$maxElement" : 1
},
{
"$minElement" : 1
}
]
]
},
"server" : "Climax-Laptop:27017"
}
> db.tester.find({i: 9, j: {$lt: 10}}).hint("i_1_j_1_k_-1").sort({k: -1}).explain()
{
"cursor" : "BtreeCursor i_1_j_1_k_-1",
"isMultiKey" : false,
"n" : 10000,
"nscannedObjects" : 10000,
"nscanned" : 10000,
"scanAndOrder" : true,
"indexOnly" : false,
"nYields" : 0,
"nChunkSkips" : 0,
"millis" : 67,
"indexBounds" : {
"i" : [
[
9,
9
]
],
"j" : [
[
-1.7976931348623157e+308,
10
]
],
"k" : [
[
{
"$maxElement" : 1
},
{
"$minElement" : 1
}
]
]
},
"server" : "Climax-Laptop:27017"
}
The index is created with:
db.tester.ensureIndex({i: 1, j: 1, k: -1})
another index: db.tester.ensureIndex({i: 1, j: 1})
and another: db.tester.ensureIndex({k: -1}).
After some preliminary testing I've found the following:
I answered the thread, please respond there.
--
You received this message because you are subscribed to the Google Groups "mongodb-user" group.
To view this discussion on the web visit https://groups.google.com/d/msg/mongodb-user/-/ihTt8Ap6ya4J.
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.
Did the data change in the collection between the first and the second?The first is slower because it required looking through 4 times as
many documents to return the results: see the nscanned values from
each explain.The index was not used for the sort as noted by the scanAndOrder:true
in the explain output. For this query you may want to create and test
this index: {k:-1, i: 1, j: 1}
> After some preliminary testing I've found the following:
>
> After dropping the last two indexes, the performance of both queries match.
> Why did this happen? Does sort use a different index than the query?
> After adding the index on i and j, no performance inconsistencies occurred.
> But after adding the index on k: -1 the performance inconsistency appeared
> again. This makes me think that the sort uses a different index. How can I
> test this?
>
> --
> You received this message because you are subscribed to the Google Groups
> "mongodb-user" group.
> To view this discussion on the web visit
> https://groups.google.com/d/msg/mongodb-user/-/ZqjOWEYlMhIJ.
> To post to this group, send email to mongod...@googlegroups.com.
> To unsubscribe from this group, send email to
On Tue, May 8, 2012 at 7:02 AM, Pieter Jordaan
>> > mongodb-user...@googlegroups.com.
>> > For more options, visit this group at
>> > http://groups.google.com/group/mongodb-user?hl=en.
>>
>>
> --
> You received this message because you are subscribed to the Google Groups
> "mongodb-user" group.
> To view this discussion on the web visit
> https://groups.google.com/d/msg/mongodb-user/-/vSqT15CpW3kJ.
>
> To post to this group, send email to mongod...@googlegroups.com.
> To unsubscribe from this group, send email to
> mongodb-user...@googlegroups.com.
On Tue, May 8, 2012 at 7:20 AM, Pieter Willem Jordaan