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_...@hotmail.com> 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....@gmail.com] > 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
