Can this query be optimized?

[Alex Paransky]

We have a query that looks like this:

db.surveyResponse.find({"surveyId" : "4f39a79601bfa841a2c06272",

"$nor" : [

{

"interviewStatus" : -99

},

{

"interviewStatus" : -50

},

{

"interviewStatus" : -60

}

]

}

)

We also have an index {surveyId:1, interviewStatus:1}

Somewhere I read that $nor will not use index, but $or will.  Does this apply here? Would it be better to rewrite this query using $or?  Are there other ways of improving performance of this query?

There are about 2 million documents in this table.  InterviewStatus is an enumeration and is sparse (about 8 integers).

Thanks.

-AP_

[Alex Huang]

try this

db.surveyResponse.find({"surveyId" : "4f39a79601bfa841a2c06272", "$nor" : [{"interviewStatus" : -99},{"interviewStatus" : -50},{"interviewStatus" : -60}]}).hint("surveyId":1, "interviewStatus":1)

any improvement pls do tell me.

在 2012年5月10日星期四UTC+8下午6时57分41秒，Alex Paransky写道：

[Adam C]

Using .explain() will help with determining what indexes are (and are not) being used:

http://www.mongodb.org/display/DOCS/Explain

Such negative matching operators are going to be less efficient in general, even with appropriate indexes, because they will have to scan far more of the index before they return than a positive match.

Adam

[Nat]

To add to Adam's comment, if your interviewStatus has only limited/small set of possible values, it would be much faster to use $in with possible values excluding the numbers you don't need.

---

From: Adam C <ad...@10gen.com>

Sender: mongod...@googlegroups.com

Date: Thu, 10 May 2012 04:36:34 -0700 (PDT)

To: <mongod...@googlegroups.com>

ReplyTo: mongod...@googlegroups.com

Subject: [mongodb-user] Re: Can this query be optimized?

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