List:General Discussion« Previous MessageNext Message »
From:Phil Date:September 9 2010 5:53pm
Subject:Trying to remove a filesort.
View as plain text  
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

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