List:General Discussion« Previous MessageNext Message »
From:Phil Date:September 9 2010 7:40pm
Subject:Re: Trying to remove a filesort.
View as plain text  
Even prior to the group by it's still not likely to ever be more than 200 or
so maximum.

I have the sort_buffer_size at 256Mb so I don't believe it's that either :(

On Thu, Sep 9, 2010 at 3:16 PM, Michael Dykman <mdykman@stripped> wrote:

> How many rows before the GROUP BY?  Group by is, in effect a sorting
> process..  perhaps that contains enough data to justify going to disk.
>
> What is the value of the variable sort_buffer_size?
>         show variables like '%sort%';
>
>  - md
>
> On Thu, Sep 9, 2010 at 3:04 PM, Phil <freedc.bok@stripped> wrote:
> > 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
> >
>
>
>
> --
>  - michael dykman
>  - mdykman@stripped
>
>  May the Source be with you.
>



-- 
Distributed Computing stats
http://stats.free-dc.org

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