CODEWITHSUNDEEP
rubymongodbmongoid
gylaz
gylaz

Reputation: 13581

Mongodb queries not using index

I'm using MongoDB 2.0.3 (via Mongoid) with a ruby (1.9.3) app.

I have a compound index that looks like:

index [
  [:attr1, Mongo::ASCENDING],
  [:attr2, Mongo::ASCENDING],
  [:attr3, Mongo::ASCENDING],
  [:attr4, Mongo::ASCENDING]
]

And a query that looks like:

Model.where(:attr3.ne => true, :attr4.ne => true).
      extras(:hint => { :attr1 => Mongo::ASCENDING,
                        :attr2 => Mongo::ASCENDING,
                        :attr3 => Mongo::ASCENDING,
                        :attr4 => Mongo::ASCENDING })

Then my code modifies the results and updates them. I have several processes running the above ruby application, all hitting the save mongodb server. I have a fairly large db (over 30mil records, 95gb in size) and is being constantly read/written to by my app.

I'm seeing a problem where sometimes the query will use the index and sometimes it won't (via db.currentOp() from mongo shell). Why is that happening and how can I fix it?

Upvotes: 1

Views: 281

Answers (1)

A. Jesse Jiryu Davis
A. Jesse Jiryu Davis

Reputation: 24017

Order matters when you create a multikey index -- if you don't include the index's first keys (attr1 and attr2) in your query, then MongoDB can't use the index. Imagine if you had a physical dictionary and you wanted to look up words whose third letter was 'a' and whose fourth letter was 'b' -- the dictionary's alphabetization wouldn't help much.

To look up documents based on attr3 and attr4, you'll need a multikey index in which attr3 and attr4 are the first keys in the index.

Upvotes: 3

Related Questions