How to speed up find_or_initialize in Rails?

Question

in my Rails 3.2.x Application there is a table called Pdata with currently more than 1.5 million records (mysql), and its growing daily. This table is collecting measurement results and is updated continuously. Every new measurement is either a new Pdata record or does update an existing one. Therefore I do

pdata = Pdata.find_or_initialize_by_a_id_and_b_id_and_timestamp(a_id,b_id,timestamp)

where a and b are basically different models where each record belongs to and they are delivered by the measurements data. There are around 500 measurements per day that needs to be updated or newly created this way. Now I found out that each find_or_initialize_by call takes around 0.7s! Is anyone aware of a faster way to find or initialize? Does anyone knows why this call takes so long?

Thanks, Andreas

Answer 1

for this case, create an index on the table with the colums you are working for, but have in mind that the order matters, use first the column that will narrows down the number of records fastest.

create the migration an add the index with your fields:

def change
  add_index :p_datas, [:a_id, :b_id, :timestamp]
end

change the order of the columns having in mind the rule mentioned before.