List:General Discussion« Previous MessageNext Message »
From:Michael Dykman Date:September 9 2010 6:57pm
Subject:Re: Trying to remove a filesort.
View as plain text  
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.
Thread
Trying to remove a filesort.Phil9 Sep
  • Re: Trying to remove a filesort.Michael Dykman9 Sep
    • Re: Trying to remove a filesort.Phil9 Sep
      • Re: Trying to remove a filesort.Michael Dykman9 Sep
        • Re: Trying to remove a filesort.Phil9 Sep
          • Re: Trying to remove a filesort.Ananda Kumar9 Sep
            • Re: Trying to remove a filesort.Phil9 Sep
              • Re: Trying to remove a filesort.Ananda Kumar9 Sep
                • Re: Trying to remove a filesort.Michael Dykman9 Sep
  • RE: Trying to remove a filesort.Travis Ard10 Sep
    • Re: Trying to remove a filesort.Phil10 Sep