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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
