StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PORails MySQL query time confusion
text
Body
copied!<p>I have a count calculation query which I am running thousands of times in my Rails app, once for each customer in the db. </p> <p>When I run the query in my MySQL client with query cache disabled the query takes last than 1ms. </p> <p>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.</p> <p>I've reduced the innodb_buffer_pool_size in order to see a change in behaviour but haven't noticed anything.</p> <p>Here's the output from the console:</p> <pre><code> 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) </code></pre> <p>There is an index on the customer_id column in that table.</p> <p>Has anyone got any suggestions as to why this would be happening?</p> <p>Thanks</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload