have u set sort_buffer_size at session level or in my.cnf. Setting high value in my.cnf, will cause mysql to run out off MEMORY and paging will happen
regards anandkl On Fri, Sep 10, 2010 at 1:10 AM, Phil <freedc....@gmail.com> wrote: > Even prior to the group by it's still not likely to ever be more than 200 > or > so maximum. > > I have the sort_buffer_size at 256Mb so I don't believe it's that either :( > > On Thu, Sep 9, 2010 at 3:16 PM, Michael Dykman <mdyk...@gmail.com> wrote: > > > 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 <freedc....@gmail.com> 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 <mdyk...@gmail.com> > > 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 <freedc....@gmail.com> 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 > > >> - mdyk...@gmail.com > > >> > > >> May the Source be with you. > > > > > > > > > > > > -- > > > Distributed Computing stats > > > http://stats.free-dc.org > > > > > > > > > > > -- > > - michael dykman > > - mdyk...@gmail.com > > > > May the Source be with you. > > > > > > -- > Distributed Computing stats > http://stats.free-dc.org >
