On average it would be between 10 and 40, certainly no more than 100.
On Thu, Sep 9, 2010 at 2:57 PM, Michael Dykman <mdyk...@gmail.com> wrote: > The filesort is probably necessary because of the number of rows in > the result set to be ordered. How many rows do you get out of this > query? > > - michael dykman > > On Thu, Sep 9, 2010 at 1:53 PM, Phil <freedc....@gmail.com> wrote: > > 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 > > > > > > -- > - michael dykman > - mdyk...@gmail.com > > May the Source be with you. > -- Distributed Computing stats http://stats.free-dc.org
