optimize query for index using between and group by
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 ?
Subject
Views
Written By
Posted
optimize query for index using between and group by
2595
August 28, 2015 03:21AM
1493
August 29, 2015 09:56AM
1000
August 31, 2015 02:34AM
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.