Adding a condition on "_id" requires to define a new index?

[Germán Leandro Vázquez]

Hello guys,

I have the query "db.col.find({field1: "val1", field2: "val2"})" and a field that covers both "field1" and "field2".

I have a new requirement which is to iterate over the resultset pages using a cursor pattern based on the collectioon "_id" field.

So, i need now to add a new field filter and sort the query: "db.col.find({field1: "val1", field2: "val2", _id: {$gt: "val3"} }).sort("_id").limit(100)".

I have ran both queries and see the execution plan results.

What I see is that the new query do uses the index and adds also a FETCH/filter stage.

So my question is if I do really have to add a new index to the collection covering also the _id field? better if I don't

First query with no "_id" filter and no sort shows:

...

"winningPlan" : {

"stage" : "LIMIT",

"limitAmount" : 0,

"inputStage" : {

"stage" : "SHARDING_FILTER",

"inputStage" : {

"stage" : "FETCH",

"inputStage" : {

"stage" : "IXSCAN",

"keyPattern" : {

...

Second query with _id filter and sort shows:

...

"winningPlan" : {

"stage" : "LIMIT",

"limitAmount" : 0,

"inputStage" : {

"stage" : "SHARDING_FILTER",

"inputStage" : {

"stage" : "FETCH",

"filter" : {

"_id" : {

"$gt" : "1234"

}

},

"inputStage" : {

"stage" : "IXSCAN",

"keyPattern" : {

...

See the new filter section on the last plan above the IXSCAN section.

Is this still a good plan? or should I need to add a new index?

(the original filter throws no more than 100k documents)

[Asya Kamsky]

I answered in the other thread (it looks like you asked this twice), so let's continue the discussion there, but this output clearly shows that you should have compound index to support the new query with _id filter and sort.

However, after using $limit, in the subsequent queries you should not use the same filter for _id but rather the last (highest) _id value from the previous query.

Asya

--
You received this message because you are subscribed to the Google Groups "mongodb-user"
group.
 
For other MongoDB technical support options, see: http://www.mongodb.org/about/support/.
---
You received this message because you are subscribed to the Google Groups "mongodb-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mongodb-user...@googlegroups.com.
To post to this group, send email to mongod...@googlegroups.com.
Visit this group at http://groups.google.com/group/mongodb-user.
To view this discussion on the web visit https://groups.google.com/d/msgid/mongodb-user/0d5c208b-09b8-490b-ad0a-4aad04c46d18%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--

Asya Kamsky
Lead Product Manager
MongoDB
Download MongoDB - mongodb.org/downloads
Free MongoDB Monitoring - cloud.mongodb.com
Free Online Education - university.mongodb.com
Get Involved - mongodb.org/community
We're Hiring! - https://www.mongodb.com/careers