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 <mdykman@stripped> 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.bok@stripped> 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
> - mdykman@stripped
>
> May the Source be with you.
>
--
Distributed Computing stats
http://stats.free-dc.org
