create Index question

[axlfu]

Here is my profiler's log
{ query: { _id: { $gt: ObjectId('4dfab509b153ba547b94d417') }, task:
{ $elemMatch: { status: 0, actionTime: { $gte: new
Date(1310356800016), $lt: new Date(1310443200016) } } } }, orderby:
{ _id: 1 } } nreturned:200 27852ms", "millis" : 27852 }
The explain is
{
"cursor" : "BtreeCursor _id_",
"nscanned" : 59889,
"nscannedObjects" : 59889,
"n" : 200,
"millis" : 24903,
"nYields" : 99,
"nChunkSkips" : 0,
"isMultiKey" : false,
"indexOnly" : false,
"indexBounds" : {
"_id" : [
[
ObjectId("4dfab509b153ba547b94d417"),
ObjectId("ffffffffffffffffffffffff")
]
]
}
}

There are range and order by in query, what index would be better?

[axlfu]

But hint to use another index give a better result:
{
"cursor" : "BtreeCursor task.status_1_task.actionTime_1",
"nscanned" : 139221,
"nscannedObjects" : 139221,
"n" : 200,
"scanAndOrder" : true,
"millis" : 7654,
"nYields" : 383,
"nChunkSkips" : 0,
"isMultiKey" : true,
"indexOnly" : false,
"indexBounds" : {
"scheme.status" : [
[
0,
0
]
],
"scheme.actionTime" : [
[
ISODate("2011-07-11T04:00:00.016Z"),
ISODate("2011-07-12T04:00:00.016Z")
]
]

[Eliot Horowitz]

nscanned is actually higher in the 2nd, so the time difference was
probably transient.

Can you try an index on { "_id" : 1 , "task.status" : 1 ,
"task.actionTime" : 1 }

> --
> 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.
>
>

[axlfu]

On Jul 12, 10:55 am, Eliot Horowitz <eliothorow...@gmail.com> wrote:
> nscanned is actually higher in the 2nd, so the time difference was
> probably transient.

No, the 1st one is also 2 times than the 2nd

>
> Can you try an index on { "_id" : 1 , "task.status" : 1 ,
> "task.actionTime" : 1 }

Well , it is soooo fast.
but the query still use {_id:1} by default.

{
"cursor" : "BtreeCursor _id_",
"nscanned" : 55178,
"nscannedObjects" : 55178,
"n" : 200,
"millis" : 168079,
"nYields" : 842,

"nChunkSkips" : 0,
"isMultiKey" : false,
"indexOnly" : false,
"indexBounds" : {
"_id" : [
[
ObjectId("4dfab509b153ba547b94d417"),
ObjectId("ffffffffffffffffffffffff")
]
]
}
}

{
"cursor" : "BtreeCursor
_id_1_task.status_1_task.actionTime_1",
"nscanned" : 62727,
"nscannedObjects" : 269,
"n" : 200,
"millis" : 158,
"nYields" : 1,

"nChunkSkips" : 0,
"isMultiKey" : true,
"indexOnly" : false,
"indexBounds" : {

"_id" : [
[
ObjectId("4dfab509b153ba547b94d417"),
ObjectId("ffffffffffffffffffffffff")
]

],
"task.status" : [
[
0,
0
]
],
"task.actionTime" : [
[

ISODate("2011-07-11T04:00:00.016Z"),
ISODate("2011-07-12T04:00:00.016Z")
]
]
}
}

It could be better if the explain could tell me the "nscanned" is
number of documents or index entries

[Eliot Horowitz]

nscanned is index entries, ncannedObjects is actual objects.

Did you use a hint for the 2nd explain?

[axlfu]

Yes. If not , query will use {_id:1}
Is it an issue?

[Eliot Horowitz]

Well it seems the other index is better for this case.
You can open a case @ http://jira.mongodb.org so we can try to figure
out why it won't choose that index.

[axlfu]

OK
https://jira.mongodb.org/browse/SERVER-3414

On Jul 12, 1:01 pm, Eliot Horowitz <eliothorow...@gmail.com> wrote:
> Well it seems the other index is better for this case.

> You can open a case @http://jira.mongodb.orgso we can try to figure