Geek Answers Handbook

Rails MySQL query time error

I have an invoice calculation request that I run thousands of times in my Rails application, once for each client in db.

When I run a query in my MySQL client with a query cache, the query takes no more than 1 ms.

However, when I run my task from the Rails console with the query output turned on, I noticed that after the first few requests, which are very fast, the time suddenly increases from less than 1 ms to about 180 ms for the rest of the requests.

I reduced innodb_buffer_pool_size to see a change in behavior, but didn't notice anything.

Here is 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 in the customer_id column of this table.

Does anyone have any suggestions as to why this is happening?

thanks

+5
4

?

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

, , , , .

+4

rails, , ? , , Aptana, ?

0

Rails ? Ruby/Rails , , , , . , .

0

Doesn't it make sense to add a cache counter to the association (read: add email_campaign_report_opens_countto your Customermodel)? Of course, you must initialize the counters during the migration, but then it should be very fast, and you don’t even need to touch the linked table while walking the client table.

0
source

More articles:


All Articles