Improve mongoDB lookup performance in rails

0
votes

I have a collection called survey_data which has following fields

  1. topic_id
  2. indicator_id
  3. population_id

The survey_data has over 2.8M records. I want to fetch the populations from populations collection for a given set of indicator_id and topic_id. But the query below is taking 20 seconds even after adding a compound index for all the fields.

db.survey_data.find({"topic_id":60,"indicator_id":16)

How can i improve the performance? May be a single query using "mongoid" for rails3 would be preferred.

Explain { "cursor" : "BtreeCursor data_source_index", "isMultiKey" : false, "n" : 2261852, "nscannedObjects" : 2261852, "nscanned" : 2261852, "nscannedObjectsAllPlans" : 2261852, "nscannedAllPlans" : 2261852, "scanAndOrder" : false, "indexOnly" : false, "nYields" : 21, "nChunkSkips" : 0, "millis" : 19952, "indexBounds" : { "data_source_id" : [ [ 60, 60 ] ] }

SurveyData Index: index({data_source_id: 1, data_source_year_id: 1, indicator_id: 1, indicator_option_id: 1, country_id: 1, provinces_state_id: 1, health_regions_id: 1, health_regions_type_id: 1, other_administrative_boundary_id: 1, sub_population_options_id: 1, reportability_id: 1}, {name:"survey_data_index", background: true })

1
ruby-on-rails-3mongodbmongoid
Please include your ensureindex statement. Also have you run an explain on the find? see: docs.mongodb.org/manual/reference/method/cursor.explain – Matthew Nichols
@MatthewNichols - Does MongoDB take 16seconds to load around 1 lakh records upon executing a query on 28lakhs documents in a collection with a single field as condition? – user1428016
Please rephrase. Lakh is not an English word. – Matthew Nichols

1 Answers

1
votes

Three things to look at:

  • topic_id doesn't appear to be in your index.
  • Try creating an index with just the fields that you are querying on, in the same order as your query.
  • Do you need to grab 100,000 records all at once? If you pull the first 100 records using limit does it speed things up?

There are some really great resources on query tuning. Here are a couple: