I wonder if anyone could help with a query which I've been unable to prevent
from using a filesort. Might be something obvious I'm overlooking!
I have a table which tracks milestones in distributed computing projects
Create Table: CREATE TABLE `boinc_milestone` (
`proj` char(6) NOT NULL,
`id` int(11) NOT NULL,
`stat_date` date NOT NULL DEFAULT '0000-00-00',
`milestone_type` char(1) NOT NULL DEFAULT '0',
`milestone` double NOT NULL DEFAULT '0',
`cpid` varchar(32) DEFAULT NULL,
`team` int(11) DEFAULT NULL,
PRIMARY KEY (`proj`,`id`,`stat_date`,`milestone`),
KEY `two` (`proj`,`stat_date`,`id`,`milestone`),
KEY `cpid` (`cpid`,`proj`,`id`,`stat_date`,`milestone`),
KEY `team` (`proj`,`team`,`id`,`stat_date`,`milestone`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
These are added to on a daily basis as users pass the various credit
milestones so for instance you can end up with rows for
1000,5000,10000,50000,1000000 etc on different dates as time goes on.
Now on one page for display I want to show the latest milestone for each
project for a particular cpid. The query I use is as follows:
select a.proj,a.id,max(stat_date),max(a.milestone) as
milestone,b.description
from boinc_milestone a join boinc_projects b on a.proj = b.proj
where cpid = '$cpid'
group by proj
order by stat_date desc
The order by causes the filesort and I can't find an easy way around it.
mysql> explain select a.proj,a.id,max(stat_date),max(a.milestone) as
milestone,b.description from boinc_milestone a join boinc_projects b on
a.proj = b.proj where cpid = 'XXX' group by proj order by stat_date\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: a
type: ref
possible_keys: PRIMARY,two,cpid,team
key: cpid
key_len: 35
ref: const
rows: 1
Extra: Using where; Using index; Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: b
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 10
ref: stats.a.proj
rows: 1
Extra: Using where
2 rows in set (0.00 sec)
I could just remove the order by altogether and perform the sort in php
afterwards I guess but any other ideas?
Thanks
Phil
--
Distributed Computing stats
http://stats.free-dc.org
