Why my index not be used while use "$query" method

[zxdinnet]

Hi:


Why my index not be used while use "$query" method:


db.Articles.find( { $query: { feedid: { $in: [ "1931302483",
"1081012247" ]} }}).explain();

{
"cursor" : "BasicCursor",
"nscanned" : 97249,
"nscannedObjects" : 97249,
"n" : 0,
"millis" : 230,
"nYields" : 1,
"nChunkSkips" : 0,
"isMultiKey" : false,
"indexOnly" : false,
"indexBounds" : {

}
}


db.Articles.find( { feedid: { $in: [ "1931302483",
"1081012247" ]} }).explain();

{
"cursor" : "BtreeCursor main_index multi",
"nscanned" : 10,
"nscannedObjects" : 9,
"n" : 9,
"millis" : 0,
"nYields" : 0,
"nChunkSkips" : 0,
"isMultiKey" : false,
"indexOnly" : false,
"indexBounds" : {
"feedid" : [
[
"1081012247",
"1081012247"
],
[
"1931302483",
"1931302483"
]
],
"articleid" : [
[
{
"$minElement" : 1
},
{
"$maxElement" : 1
}
]
],
"issuetime" : [
[
{
"$minElement" : 1
},
{
"$maxElement" : 1
}
]
]
}
}
"

best regards
zxdinnet

[Eliot Horowitz]

Find is already wrapping query in $query so it's getting double wrapped.

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

[zxdinnet]

I'm using C API for read data from mongodb, so some query have to use
"$query" and "$orderby", but these query cann't using index be
created.

query as :
bson_init( query );
bson_append_start_object( query, "$query" );
bson_append_int( query, "age", 24 );
bson_append_finish_object( query );

bson_append_start_object( query, "$orderby" );
bson_append_int( query, "name", 1);
bson_append_finish_object( query );
bson_finish( query );

multikey:
{
"age" : 1,
"name" : 1
}

this query cann't using this multikey(explain)

How should I do it?

On 9月9日, 下午2时00分, Eliot Horowitz <eliothorow...@gmail.com> wrote:
> Find is already wrapping query in $query so it's getting double wrapped.
>

> > For more options, visit this group athttp://groups.google.com/group/mongodb-user?hl=en.- 隐藏被引用文字 -
>
> - 显示引用的文字 -

[Eliot Horowitz]

Can you start mongod with -vvv so you can see exactly what is being sent?
Then run an explain on that and send?

[zxdinnet]

Thank you for your reply:)

I'm restart mongod with -vvvvv, and oupt is:

Sat Sep 10 16:16:31 [conn20] query: test.testindex{ $query: { age:
{ $in: [ 24, 49278.5, 48766.5, 49022.5 ] } }, $orderby: { name: -1 } }
Sat Sep 10 16:16:31 [conn20] used cursor: 0xec7cf0
Sat Sep 10 16:16:31 [conn20] query test.testindex scanAndOrder reslen:
212 nreturned:4 0ms
Sat Sep 10 16:16:31 [conn20] query: test.testindex{ $query: { $query:
{ age: { $in: [ 24, 49278.5, 48766.5, 49022.5 ] } }, $orderby: { name:
-1 } }, $explain: true }
Sat Sep 10 16:16:31 [conn20] used cursor: 0xe2ae60
Sat Sep 10 16:16:31 [conn20] query test.testindex reslen:313 nreturned:
1 69ms

multikey is:
{
"age": 1,
"name": 1
}

explain is:
[cursor] => BasicCursor
[nscanned] => 100003
[nscannedObjects] => 100003
[n] => 0
[millis] => 69
[nYields] => 0
[nChunkSkips] => 0
[isMultiKey] =>
[indexOnly] =>
[indexBounds] => Array
(
)

[allPlans] => Array
(
[0] => Array
(
[cursor] => BasicCursor
[indexBounds] => Array
(
)

)

)

[oldPlan] => Array
(
[cursor] => BasicCursor
[indexBounds] => Array
(
)

)


Best regards
zxdinnet

> >> > For more options, visit this group athttp://groups.google.com/group/mongodb-user?hl=en.-隐藏被引用文字 -

[Eliot Horowitz]

What indexes do you have?

2011/9/10 zxdinnet <zxdi...@gmail.com>:

[zxdinnet]

my index :

> db.testindex.getIndexes();
[
{
"name" : "_id_",
"ns" : "test.testindex",
"key" : {
"_id" : 1
},
"v" : 0
},
{
"_id" : ObjectId("4e6b11f9379e192815000000"),
"ns" : "test.testindex",
"key" : {
"age" : 1,
"name" : 1
},
"background" : 1,
"name" : "main1_index",
"v" : 0
}
]

On 9月10日, 下午9时48分, Eliot Horowitz <el...@10gen.com> wrote:
> What indexes do you have?
>

> 2011/9/10 zxdinnet <zxdin...@gmail.com>:

[Eliot Horowitz]

Something is odd as in the log it always says 0ms, bu the explain is more.
From the shell could you do:

db.testindex.stats()
db.testindex.find( { age: { $in: [ 24, 49278.5, 48766.5, 49022.5 ] } }
).,sort( { name : -1 } ).explain()

2011/9/10 zxdinnet <zxdi...@gmail.com>:

[zxdinnet]

> db.testindex.stats();
{
"ns" : "test.testindex",
"count" : 100003,
"size" : 4600180,
"avgObjSize" : 46.00041998740038,
"storageSize" : 11182080,
"numExtents" : 6,
"nindexes" : 2,
"lastExtentSize" : 8388608,
"paddingFactor" : 1,
"flags" : 1,
"totalIndexSize" : 9887744,
"indexSizes" : {
"_id_" : 5718016,
"main1_index" : 4169728
},
"ok" : 1
}

> db.testindex.find( { age: { $in: [ 24, 49278.5, 48766.5, 49022.5 ] } } ).sort( { name : -1 } ).explain();
{
"cursor" : "BtreeCursor main1_index multi",
"nscanned" : 7,
"nscannedObjects" : 4,
"n" : 4,
"scanAndOrder" : true,

"millis" : 0,
"nYields" : 0,
"nChunkSkips" : 0,
"isMultiKey" : false,
"indexOnly" : false,
"indexBounds" : {

"age" : [
[
24,
24
],
[
48766.5,
48766.5
],
[
49022.5,
49022.5
],
[
49278.5,
49278.5
]
],
"name" : [
[
{

"$minElement" : 1
},
{
"$maxElement" : 1
}
]
]
}
}

On 9月10日, 下午9时59分, Eliot Horowitz <el...@10gen.com> wrote:
> Something is odd as in the log it always says 0ms, bu the explain is more.
> From the shell could you do:
>
> db.testindex.stats()
> db.testindex.find( { age: { $in: [ 24, 49278.5, 48766.5, 49022.5 ] } }
> ).,sort( { name : -1 } ).explain()
>

> 2011/9/10 zxdinnet <zxdin...@gmail.com>:

[Eliot Horowitz]

So that seems to work.
Can you run mongod with -vvv, then copy the query in c?

2011/9/10 zxdinnet <zxdi...@gmail.com>:

[Francois Baligant]

I believe I stumbed on the same problem when investigating some
performance issue only when using the Mongo PHP driver.

The Mongo PHP driver will wrap the query in a $query construct if
there is any option used (addOption)

The following behaviour is same with Mongod 1.6.5 and 2.0.0

A first query working as expected with the index:

> db.news_item.find()._addSpecial('query', {actor_id:32126}).explain();
{
"cursor" : "BtreeCursor actor_id_1",
"nscanned" : 10,
"nscannedObjects" : 10,
"n" : 10,
"millis" : 0,
"indexBounds" : {
"actor_id" : [
[
32126,
32126
]
]
}
}

127.0.0.1:35273 -->> 127.0.0.1:27017 maresidence.news_item 96 bytes
id:c569863a 3312027194
query: { query: { actor_id: 32126.0 }, $explain: true } ntoreturn: 0
ntoskip: 0
127.0.0.1:27017 <<-- 127.0.0.1:35273 426 bytes id:7a6959b3
2053724595 - 3312027194
reply n:1 cursorId: 0
{ cursor: "BtreeCursor actor_id_1", nscanned: 10, nscannedObjects:
10, n: 10, millis: 0, indexBounds: { actor_id: { 0: { 0: 32126.0, 1:
32126.0 } } }, allPlans: { 0: { cursor: "BtreeCursor actor_id_1",
indexBounds: { actor_id: { 0: { 0: 32126.0, 1: 32126.0 } } } } },
oldPlan: { cursor: "BtreeCursor actor_id_1", indexBounds: { actor_id:
{ 0: { 0: 32126.0, 1: 32126.0 } } } } }

Another query with the $query construct, index is bypassed:

> db.news_item.find({$query: {actor_id:32126}}).explain();
{
"cursor" : "BasicCursor",
"nscanned" : 92154,
"nscannedObjects" : 92154,
"n" : 0,
"millis" : 173,
"indexBounds" : {

}
}

127.0.0.1:35273 -->> 127.0.0.1:27017 maresidence.news_item 109
bytes id:c569863b 3312027195
query: { query: { $query: { actor_id: 32126.0 } }, $explain: true }
ntoreturn: 0 ntoskip: 0
127.0.0.1:27017 <<-- 127.0.0.1:35273 258 bytes id:7a6959b4
2053724596 - 3312027195
reply n:1 cursorId: 0
{ cursor: "BasicCursor", nscanned: 92154, nscannedObjects: 92154, n:
0, millis: 173, indexBounds: {}, allPlans: { 0: { cursor:
"BasicCursor", indexBounds: {} } }, oldPlan: { cursor: "BasicCursor",
indexBounds: {} } }

Both queries send back the expected results, the $query one is much
slower.

And as a bonus:

> db.news_item.find()._addSpecial('query', {actor_id:32126}).count();
10
> db.news_item.find({}, {actor_id:32126})._addSpecial('query', {actor_id:32126})
{ "_id" : ObjectId("4c638c846fd62f7807930000"), "actor_id" : 32126 }
{ "_id" : ObjectId("4c638c846fd62f7807970000"), "actor_id" : 32126 }
{ "_id" : ObjectId("4c638ca66fd62f7807780300"), "actor_id" : 32126 }
{ "_id" : ObjectId("4c638cfa6fd62f7807490b00"), "actor_id" : 32126 }
{ "_id" : ObjectId("4c638d116fd62f7807e40c00"), "actor_id" : 32126 }
{ "_id" : ObjectId("4c638f236fd62f78078e2400"), "actor_id" : 32126 }
{ "_id" : ObjectId("4c638f346fd62f7807462500"), "actor_id" : 32126 }
{ "_id" : ObjectId("4c69533e6fd62f5d1c480800"), "actor_id" : 32126 }
{ "_id" : ObjectId("4d6a7e9ff43cf9ea0f000000"), "actor_id" : 32126 }
{ "_id" : ObjectId("4d8087cc8ac5138218010000"), "actor_id" : 32126 }
>

> db.news_item.find({$query: {actor_id:32126}}).count()
0
> db.news_item.find({$query: {actor_id:32126}}, {actor_id:1})
{ "_id" : ObjectId("4c638c846fd62f7807930000"), "actor_id" : 32126 }
{ "_id" : ObjectId("4c638c846fd62f7807970000"), "actor_id" : 32126 }
{ "_id" : ObjectId("4c638ca66fd62f7807780300"), "actor_id" : 32126 }
{ "_id" : ObjectId("4c638cfa6fd62f7807490b00"), "actor_id" : 32126 }
{ "_id" : ObjectId("4c638d116fd62f7807e40c00"), "actor_id" : 32126 }
{ "_id" : ObjectId("4c638f236fd62f78078e2400"), "actor_id" : 32126 }
{ "_id" : ObjectId("4c638f346fd62f7807462500"), "actor_id" : 32126 }
{ "_id" : ObjectId("4c69533e6fd62f5d1c480800"), "actor_id" : 32126 }
{ "_id" : ObjectId("4d6a7e9ff43cf9ea0f000000"), "actor_id" : 32126 }
{ "_id" : ObjectId("4d8087cc8ac5138218010000"), "actor_id" : 32126 }

count() is wrong with $query construct.

Best regards,
Francois