List:General Discussion« Previous MessageNext Message »
From:Phil Date:September 9 2010 10:47pm
Subject:Re: Trying to remove a filesort.
View as plain text  
Thanks! I did not know that.

Just tried it and indeed the Created_tmp_disk_tables did not increase, just
the Created_tmp_tables increased by +1. Still not perfect, but it's better
than I thought and at least in memory.

And for the previous mails, I'm not sure why I ever had the sort_buffer_size
that high, have reduced it now.

On Thu, Sep 9, 2010 at 6:30 PM, Travis Ard <travis_ard@stripped> wrote:

> When the explain output says "Using filesort", it doesn't necessarily mean
> it is sorting on disk.  It could still be sorting in memory and, thus, be
> reasonably fast.  You might check the value of Created_tmp_disk_tables
> before and after your query to see for sure.
>
> -Travis
>
> -----Original Message-----
> From: Phil [mailto:freedc.bok@stripped]
> Sent: Thursday, September 09, 2010 11:54 AM
> To: mysql
> Subject: Trying to remove a filesort.
>
> 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
>
>


-- 
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