Account Options

  1. Sign in
The old Google Groups will be going away soon, but your browser is incompatible with the new version.
Google Groups Home
« Groups Home
mongodb-user
Home
+ new post
About this group
Join this group
Why my index not be used while use "$query" method
Options
There are currently too many topics in this group that display first. To make this topic appear first, remove this option from another topic.
There was an error processing your request. Please try again.
flag
   11 messages - Collapse all  -  Translate all to Translated (View all originals)
The group you are posting to is a Usenet group. Messages posted to this group will make your email address visible to anyone on the Internet.
Your reply message has not been sent.
Your post was successful
 
From:
To:
Cc:
Followup To:
Add Cc | Add Followup-to | Edit Subject
Subject:
Validation:
For verification purposes please type the characters you see in the picture below or the numbers you hear by clicking the accessibility icon. Listen and type the numbers you hear
 
zxdinnet  
View profile  
 More options Sep 8 2011, 8:10 pm
From: zxdinnet <zxdin...@gmail.com>
Date: Thu, 8 Sep 2011 17:10:40 -0700 (PDT)
Local: Thurs, Sep 8 2011 8:10 pm
Subject: Why my index not be used while use "$query" method
Print | Individual message | Show original | Report this message | Find messages by this author
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


 
You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before posting.
You do not have the permission required to post.
Eliot Horowitz  
View profile  
 More options Sep 9 2011, 2:00 am
From: Eliot Horowitz <eliothorow...@gmail.com>
Date: Fri, 9 Sep 2011 02:00:02 -0400
Local: Fri, Sep 9 2011 2:00 am
Subject: Re: [mongodb-user] Why my index not be used while use "$query" method
Print | Individual message | Show original | Report this message | Find messages by this author
Find is already wrapping query in $query so it's getting double wrapped.

On Sep 8, 2011, at 8:10 PM, zxdinnet <zxdin...@gmail.com> wrote:


 
You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before posting.
You do not have the permission required to post.
zxdinnet  
View profile  
 More options Sep 9 2011, 3:45 am
From: zxdinnet <zxdin...@gmail.com>
Date: Fri, 9 Sep 2011 00:45:10 -0700 (PDT)
Local: Fri, Sep 9 2011 3:45 am
Subject: Re: Why my index not be used while use "$query" method
Print | Individual message | Show original | Report this message | Find messages by this author
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:


 
You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before posting.
You do not have the permission required to post.
Eliot Horowitz  
View profile  
 More options Sep 10 2011, 1:03 am
From: Eliot Horowitz <el...@10gen.com>
Date: Sat, 10 Sep 2011 01:03:16 -0400
Local: Sat, Sep 10 2011 1:03 am
Subject: Re: [mongodb-user] Re: Why my index not be used while use "$query" method
Print | Individual message | Show original | Report this message | Find messages by this author
Can you start mongod with -vvv so you can see exactly what is being sent?
Then run an explain on that and send?


 
You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before posting.
You do not have the permission required to post.
zxdinnet  
View profile  
 More options Sep 10 2011, 4:24 am
From: zxdinnet <zxdin...@gmail.com>
Date: Sat, 10 Sep 2011 01:24:39 -0700 (PDT)
Local: Sat, Sep 10 2011 4:24 am
Subject: Re: Why my index not be used while use "$query" method
Print | Individual message | Show original | Report this message | Find messages by this author
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

On 9月10日, 下午1时03分, Eliot Horowitz <el...@10gen.com> wrote:


 
You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before posting.
You do not have the permission required to post.
Eliot Horowitz  
View profile  
 More options Sep 10 2011, 9:48 am
From: Eliot Horowitz <el...@10gen.com>
Date: Sat, 10 Sep 2011 09:48:18 -0400
Local: Sat, Sep 10 2011 9:48 am
Subject: Re: [mongodb-user] Re: Why my index not be used while use "$query" method
Print | Individual message | Show original | Report this message | Find messages by this author
What indexes do you have?

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


 
You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before posting.
You do not have the permission required to post.
zxdinnet  
View profile  
 More options Sep 10 2011, 9:52 am
From: zxdinnet <zxdin...@gmail.com>
Date: Sat, 10 Sep 2011 06:52:04 -0700 (PDT)
Local: Sat, Sep 10 2011 9:52 am
Subject: Re: Why my index not be used while use "$query" method
Print | Individual message | Show original | Report this message | Find messages by this author

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:


 
You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before posting.
You do not have the permission required to post.
Eliot Horowitz  
View profile  
 More options Sep 10 2011, 9:59 am
From: Eliot Horowitz <el...@10gen.com>
Date: Sat, 10 Sep 2011 09:59:00 -0400
Subject: Re: [mongodb-user] Re: Why my index not be used while use "$query" method
Print | Individual message | Show original | Report this message | Find messages by this author
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>:


 
You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before posting.
You do not have the permission required to post.
zxdinnet  
View profile  
 More options Sep 10 2011, 10:04 am
From: zxdinnet <zxdin...@gmail.com>
Date: Sat, 10 Sep 2011 07:04:16 -0700 (PDT)
Local: Sat, Sep 10 2011 10:04 am
Subject: Re: Why my index not be used while use "$query" method
Print | Individual message | Show original | Report this message | Find messages by this author
> 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:

...

read more »


 
You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before posting.
You do not have the permission required to post.
Eliot Horowitz  
View profile  
 More options Sep 10 2011, 11:32 pm
From: Eliot Horowitz <el...@10gen.com>
Date: Sat, 10 Sep 2011 23:32:54 -0400
Local: Sat, Sep 10 2011 11:32 pm
Subject: Re: [mongodb-user] Re: Why my index not be used while use "$query" method
Print | Individual message | Show original | Report this message | Find messages by this author
So that seems to work.
Can you run mongod with -vvv, then copy the query in c?

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

...

read more »


 
You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before posting.
You do not have the permission required to post.
Francois Baligant  
View profile  
 More options Oct 18 2011, 8:33 pm
From: Francois Baligant <fbalig...@synalabs.com>
Date: Tue, 18 Oct 2011 17:33:44 -0700 (PDT)
Local: Tues, Oct 18 2011 8:33 pm
Subject: Re: Why my index not be used while use "$query" method
Print | Individual message | Show original | Report this message | Find messages by this author
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


 
You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before posting.
You do not have the permission required to post.
End of messages
« Back to Discussions « Newer topic     Older topic »

Create a group - Google Groups - Google Home - Terms of Service - Privacy Policy
©2013 Google