Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Can someone explain or direct me how execution on indexes happen with different constants at intervals in Mysql. I notice only for the first execution on the table it takes time, after that with different constants it executes the query very quickly. I would like to know how to execute the query in such a way that it should take same amount of time every time it executes with different constants, is there a way to set some parameter off / on?

Query executed time : 9 mins.

mysql>  EXPLAIN SELECT     chargetype,    COUNT(br.`id`),   SUM(br.`charge`)  FROM
billingreport AS br  WHERE     br.`addeddate` BETWEEN '2013-02-01 00:00:00'    AND '2013-02-28 23:59:59'
AND br.`status` = 'success'    AND br.`idvendor` = 10     GROUP BY chargetype \G

 *************************** 1. row ***************************
       id: 1
select_type: SIMPLE
    table: br
     type: index_merge
 possible_keys: NewIndex3,NewIndex6,idx_br_status
      key: NewIndex3,idx_br_status
   key_len: 4,1
      ref: NULL
     rows: 2887152
    Extra: Using intersect(NewIndex3,idx_br_status); Using where; Using temporary; Using filesort
1 row in set (0.00 sec)

Query executed time : 18 Secs.

 mysql>  EXPLAIN SELECT     chargetype,    COUNT(br.`id`),   SUM(br.`charge`)  
 FROM     billingreport AS br  WHERE     br.`addeddate` BETWEEN '2013-01-01 00:00:00'    AND 
 '2013-01-31 23:59:59'    AND br.`status` = 'success'    AND br.`idvendor` = 10  GROUP BY chargetype \G
*************************** 1. row ***************************
       id: 1
  select_type: SIMPLE
    table: br
     type: index_merge
 possible_keys: NewIndex3,NewIndex6,idx_br_status
      key: NewIndex3,idx_br_status
  key_len: 4,1
      ref: NULL
     rows: 3004089
    Extra: Using intersect(NewIndex3,idx_br_status); Using where; Using temporary; Using filesort
 1 row in set (0.01 sec)
share|improve this question
2  
For me the plans look identical, except the no of rows. But I'm not a MySQL user.. Isn't MySQL using caching, anyway? So the first execution is long and the others are fast? Or is it always the first version is slow and second version is fast? What happens if you change the order of the executions? PS: do you really need 3 mil rows? –  Marian Mar 22 '13 at 8:47
    
can you provide your create table for billingreport ? –  altmannmarcelo May 21 '13 at 10:39
    
What indexes do you have on the table? Either a (status, idvendor, chargetype, addeddate) or (status, idvendor, addeddate, chargetype) index would be quite efficient. –  ypercube May 21 '13 at 11:33
add comment

closed as unclear what you're asking by Mark Storey-Smith, ypercube, StanleyJohns, dezso, Mat Jul 22 '13 at 10:42

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question.If this question can be reworded to fit the rules in the help center, please edit the question.

1 Answer

You're probably seeing the effect of the InnoDB buffer cache. There's no straightforward way to clear it, but if it's a test system you can restart MySQL or set innodb_max_dirty_pages_pct=0.

The operating system's buffer cache might play into this as well.

share|improve this answer
add comment

Not the answer you're looking for? Browse other questions tagged or ask your own question.