Phil wrote:
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
With no where clause, and aggregate functions, it is faster to do a full
table read, and the sort the aggregated results.
--
Gerald L. Clark
Sr. V.P. Development
Supplier Systems Corporation
Unix since 1982
Linux since 1992
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
