From: Michael Dykman
Date: September 9 2010 6:57pm
Subject: Re: Trying to remove a filesort.
List-Archive: http://lists.mysql.com/mysql/222904
Message-Id: <AANLkTim97fV0PM7yTUezE3Dx-Y5C3pWrCnGUen+-ugVK@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

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 prev=
ent
> 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` (
> =A0`proj` char(6) NOT NULL,
> =A0`id` int(11) NOT NULL,
> =A0`stat_date` date NOT NULL DEFAULT '0000-00-00',
> =A0`milestone_type` char(1) NOT NULL DEFAULT '0',
> =A0`milestone` double NOT NULL DEFAULT '0',
> =A0`cpid` varchar(32) DEFAULT NULL,
> =A0`team` int(11) DEFAULT NULL,
> =A0PRIMARY KEY (`proj`,`id`,`stat_date`,`milestone`),
> =A0KEY `two` (`proj`,`stat_date`,`id`,`milestone`),
> =A0KEY `cpid` (`cpid`,`proj`,`id`,`stat_date`,`milestone`),
> =A0KEY `team` (`proj`,`team`,`id`,`stat_date`,`milestone`)
> ) ENGINE=3DMyISAM DEFAULT CHARSET=3Dlatin1
>
> 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
> =A0 =A0 =A0 =A0 from boinc_milestone a join boinc_projects b on a.proj =
=3D b.proj
> =A0 =A0 =A0 =A0 where cpid =3D '$cpid'
> =A0 =A0 =A0 =A0 group by proj
> =A0 =A0 =A0 =A0 order by stat_date desc
>
> The order by causes the filesort and I can't find an easy way around it.
>
> mysql> explain =A0select 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 =3D b.proj where cpid =3D 'XXX' group by proj order by stat_date\G
> *************************** 1. row ***************************
> =A0 =A0 =A0 =A0 =A0 id: 1
> =A0select_type: SIMPLE
> =A0 =A0 =A0 =A0table: a
> =A0 =A0 =A0 =A0 type: ref
> possible_keys: PRIMARY,two,cpid,team
> =A0 =A0 =A0 =A0 =A0key: cpid
> =A0 =A0 =A0key_len: 35
> =A0 =A0 =A0 =A0 =A0ref: const
> =A0 =A0 =A0 =A0 rows: 1
> =A0 =A0 =A0 =A0Extra: Using where; Using index; Using temporary; Using fi=
lesort
> *************************** 2. row ***************************
> =A0 =A0 =A0 =A0 =A0 id: 1
> =A0select_type: SIMPLE
> =A0 =A0 =A0 =A0table: b
> =A0 =A0 =A0 =A0 type: eq_ref
> possible_keys: PRIMARY
> =A0 =A0 =A0 =A0 =A0key: PRIMARY
> =A0 =A0 =A0key_len: 10
> =A0 =A0 =A0 =A0 =A0ref: stats.a.proj
> =A0 =A0 =A0 =A0 rows: 1
> =A0 =A0 =A0 =A0Extra: 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
>



--=20
=A0- michael dykman
=A0- mdykman@stripped

=A0May the Source be with you.