I have a table as follows containing approx 1.5M rows. I pull data from it based on the CPID and insert into an 'overall' table. After breaking down the statement somewhat, an explain still gives a filesort. Is there any way to avoid a filesort for this query ?
mysql> explain select a.cpid ,sum(a.metric1) ,sum(a.metric2),0,0,a.country,min(a.create_date),min(a.create_time),a.nick from boinc_user a group by a.cpid order by a.cpid; +----+-------------+-------+------+---------------+------+---------+------+---------+---------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+---------+---------------------------------+ | 1 | SIMPLE | a | ALL | NULL | NULL | NULL | NULL | 1443130 | Using temporary; Using filesort | +----+-------------+-------+------+---------------+------+---------+------+---------+---------------------------------+ 1 row in set (0.00 sec) CREATE TABLE `boinc_user` ( `proj` char(6) NOT NULL, `id` int(11) NOT NULL default '0', `stat_date` date NOT NULL default '0000-00-00', `nick` varchar(50) character set latin1 collate latin1_bin NOT NULL default '', `country` varchar(50) NOT NULL default '', `cpid` varchar(50) NOT NULL default '', `url` varchar(50) default NULL, `create_date` int(11) NOT NULL, `create_time` bigint(20) NOT NULL, `has_profile` char(1) NOT NULL, `team0` int(11) default NULL, `team1` int(11) default NULL, `metric1` double NOT NULL default '0', `metric2` double NOT NULL default '0', `metric3` double NOT NULL default '0', `metric4` double default NULL, `today` double default '0' PRIMARY KEY (`proj`,`id`), KEY `trank` (`proj`,`team0`,`metric1`,`id`), KEY `forstats` (`proj`,`metric1`,`id`), KEY `name` (`proj`,`id`), KEY `racrank` (`proj`,`metric2`,`id`), KEY `cpid` (`cpid`,`proj`), KEY `today` (`proj`,`today`,`id`), KEY `prank` (`proj`,`projrank0`,`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 Regards Phil -- Help build our city at http://free-dc.myminicity.com !