Query is fast in MySQL but slow when run from Rails ActiveRecord

I have a query that runs very fast in the MySQL console but very slow when I run it using Rails Active Record. This is the query that run against a table of 7 million records:

select broker_id,count(abserror),avg(abserror) from fc_estimates where ( fpe > '2000-05-28') and ( fpe < '2003-06-30') group by broker_id order by broker_id;

That takes 3 minutes to run.

Then I run this query in Rails Active Record:

stats = Estimate. select([ "broker_id", "count(abserror) as abserror_count", "avg(abserror) as abserror_avg" ]). where( :fpe => ((fpe-1098).to_date..(fpe+30).to_date)) group("broker_id"). order("broker_id")

which generates this sql (output from to_sql)

SELECT broker_id, count(abserror) as abserror_count, avg(abserror) as abserror_avg FROM fc_estimates WHERE (fc_estimates.fpe BETWEEN '2000-05-28' AND '2003-06-30') GROUP BY broker_id ORDER BY broker_id

and takes 1 hour 40 minutes to run. It returns 250 records.

I am using Windows 7, MySQl 5.1, Ruby 1.8.7, Active开发者_如何转开发Record 3.04, mysql2 gem 0.2.6

These are InnoDB tables and I have increased the innodb_buffer_pool_size to 480M (which did help with other queries). One thing I do observe is that the MySQL memory use builds up to about 500M and then there is a lot of disk activity (page swapping). Which does explain somehing.

But still why I am getting such poor performance when the same query run in MySQL console is just taking 3 minutes? Thanks for any ideas or anyone who has experienced a similar situation.

UPDATE 2011-02-24

I updated to MySQL 5.5. Now my query in the console runs in about 1min40secs. And using ActiveRecords takes about 40mins.

There's much more running in your ruby code than just a SQL Query. I'm not an Ruby Jedi but I can point out some stuff.

Windows is not the best place to work with the MRI. Maybe you should try out 1.9.2 or JRuby - or even switching to some *nix OS.

(fpe-1098).to_date..(fpe+30).to_date) builds a Range instance for the date intervals. Maybe you should try a different syntax, ie: ['fpe > ? AND fpe < ?'(fpe-1098),(fpe+30)] - so less objects will be created.

Since you're not retrieving Estimate instances, instead of running the query with the model class you can pass the sql generated to ActiveRecord::Base.connection.execute. Maybe there will be less memory usage and objects created.