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 <[email protected]> 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 <[email protected]> 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 <[email protected]> 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
>> - [email protected]
>>
>> May the Source be with you.
>
>
>
> --
> Distributed Computing stats
> http://stats.free-dc.org
>
--
- michael dykman
- [email protected]
May the Source be with you.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[email protected]
