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)
