MySQL Forums
Forum List  »  Optimizer & Parser

optimize query for index using between and group by
Posted by: Raghavendra raghu
Date: August 28, 2015 03:21AM

CREATE TABLE ad_history (
ad_history_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
tpc_checkmaster varchar(40) DEFAULT NULL,
tpc_checkdate int(10) unsigned NOT NULL DEFAULT '0',
tpc_checkmasteraction
PRIMARY KEY (ad_history_id),
KEY INDEX_TPC_MASTER_ACTION_TIME (tpc_checkdate),
KEY idx_tpc_checkmaster_action (tpc_checkmaster,tpc_checkmasteraction),
) ENGINE=MyISAM AUTO_INCREMENT=12582

SELECT
tpc_checkmaster ,
tpc_checkmasteraction,
COUNT(*) AS adcount,
AVG(tpc_clearingtime) AS clearingtime
FROM
ad_history
WHERE
tpc_checkdate BETWEEN 1408437444 AND 1408519225
GROUP BY tpc_checkmaster , tpc_checkmasteraction
ORDER BY tpc_checkmaster , tpc_checkmasteraction


explain plan:
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: admin_action_ad_history
type: range
possible_keys: INDEX_TPC_MASTER_ACTION_TIME,idx_tpc_checkmaster_action
key: INDEX_TPC_MASTER_ACTION_TIME
key_len: 4
ref: NULL
rows: 85
Extra: Using index condition; Using temporary; Using filesort
1 row in set (0.01 sec)


how avoid usage of file sort ?

Options: ReplyQuote


Subject
Views
Written By
Posted
optimize query for index using between and group by
2595
August 28, 2015 03:21AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.