Rails MySQL query time confusion

Question

Hi,

I have a count calculation query which I am running thousands of times in my Rails app, once for each customer in the db.

When I run the query in my MySQL client with query cache disabled the query takes last than 1ms.

However, when I run my task from the Rails console with query output enabled I've noticed that after the first few queries which are very quick the time suddenly shoots up from less than 1ms to about 180ms for the remainder of the queries.

I've reduced the innodb_buffer_pool_size in order to see a change in behaviour but haven't noticed anything.

Here's the output from the console:

  EmailCampaignReport::Open Columns (143.2ms)   SHOW FIELDS FROM `email_campaign_report_opens`
  SQL (0.3ms)   SELECT count(*) AS count_all FROM `email_campaign_report_opens` WHERE (customer_id = 332330) 
  SQL (0.2ms)   SELECT count(*) AS count_all FROM `email_campaign_report_opens` WHERE (customer_id = 333333) 
  SQL (0.2ms)   SELECT count(*) AS count_all FROM `email_campaign_report_opens` WHERE (customer_id = 332661) 
  SQL (0.1ms)   SELECT count(*) AS count_all FROM `email_campaign_report_opens` WHERE (customer_id = 332326) 
  SQL (0.1ms)   SELECT count(*) AS count_all FROM `email_campaign_report_opens` WHERE (customer_id = 332665) 
  SQL (0.2ms)   SELECT count(*) AS count_all FROM `email_campaign_report_opens` WHERE (customer_id = 336027) 
  SQL (0.2ms)   SELECT count(*) AS count_all FROM `email_campaign_report_opens` WHERE (customer_id = 333001) 
  SQL (0.2ms)   SELECT count(*) AS count_all FROM `email_campaign_report_opens` WHERE (customer_id = 331983) 
  SQL (0.1ms)   SELECT count(*) AS count_all FROM `email_campaign_report_opens` WHERE (customer_id = 332668) 
  SQL (0.1ms)   SELECT count(*) AS count_all FROM `email_campaign_report_opens` WHERE (customer_id = 332316) 
  SQL (0.1ms)   SELECT count(*) AS count_all FROM `email_campaign_report_opens` WHERE (customer_id = 332325) 
  SQL (0.1ms)   SELECT count(*) AS count_all FROM `email_campaign_report_opens` WHERE (customer_id = 331995) 
  SQL (0.2ms)   SELECT count(*) AS count_all FROM `email_campaign_report_opens` WHERE (customer_id = 334007) 
  SQL (0.2ms)   SELECT count(*) AS count_all FROM `email_campaign_report_opens` WHERE (customer_id = 333326) 
  SQL (0.1ms)   SELECT count(*) AS count_all FROM `email_campaign_report_opens` WHERE (customer_id = 332998) 
  SQL (183.9ms)   SELECT count(*) AS count_all FROM `email_campaign_report_opens` WHERE (customer_id = 334673) 
  SQL (183.7ms)   SELECT count(*) AS count_all FROM `email_campaign_report_opens` WHERE (customer_id = 336751) 
  SQL (183.6ms)   SELECT count(*) AS count_all FROM `email_campaign_report_opens` WHERE (customer_id = 333334) 
  SQL (186.3ms)   SELECT count(*) AS count_all FROM `email_campaign_report_opens` WHERE (customer_id = 332663) 
  SQL (183.7ms)   SELECT count(*) AS count_all FROM `email_campaign_report_opens` WHERE (customer_id = 332328) 
  SQL (186.3ms)   SELECT count(*) AS count_all FROM `email_campaign_report_opens` WHERE (customer_id = 332659) 

There is an index on the customer_id column in that table.

Has anyone got any suggestions as to why this would be happening?

Thanks

Answer 1

Does this happen in your rails app as well, or does this just happen when you run it in the console? Also, are you using a client such as Aptana or are you running this in a shell?

Answer 2

What version of Rails is this? Depending on your version, and your Ruby/Rails code, you may be caching a lot of data without using it, and after a while it has to do garbage collection before getting new data, which may explain the delay. This is a guess, mind you.

Answer 3

Wouldn't it make sense to add a counter cache to the association (read: add a email_campaign_report_opens_count to your Customer model)? Of course you have to initialize the counters during migration but then it should be really fast and you even don't need to touch the associated table while walking the customers table.

Answer 4

Why not run just one query?

SELECT customer_id, count(*) AS count_all FROM `email_campaign_report_opens` GROUP BY customer_id;

If you have so many records that you are worried about returning them all then do it in batches, but I just don't understand why you would really want to run this query for every customer.