Trying to remove a filesort.

--0016364266bba27849048fd7506c
Content-Type: text/plain; charset=ISO-8859-1

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

--0016364266bba27849048fd7506c--

Re: Trying to remove a filesort.

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

=A0May the Source be with you.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Trying to remove a filesort.

--0016367659eb313947048fd84fe6
Content-Type: text/plain; charset=ISO-8859-1

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 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 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
> - mdykman@gmail.com
>
> May the Source be with you.
>



--
Distributed Computing stats
http://stats.free-dc.org

--0016367659eb313947048fd84fe6--

Re: Trying to remove a filesort.

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 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 wrote:
>>
>> The filesort is probably necessary because of the number of rows in
>> the result set to be ordered. =A0How many rows do you get out of this
>> query?
>>
>> =A0- michael dykman
>>
>> On Thu, Sep 9, 2010 at 1:53 PM, Phil 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 projec=
ts
>> >
>> > 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 ea=
ch
>> > 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 i=
t.
>> >
>> > mysql> explain =A0select a.proj,a.id,max(stat_date),max(a.milestone) a=
s
>> > milestone,b.description from boinc_milestone a join boinc_projects b o=
n
>> > a.proj =3D b.proj where cpid =3D 'XXX' group by proj order by stat_dat=
e\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=
filesort
>> > *************************** 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 ph=
p
>> > afterwards I guess but any other ideas?
>> >
>> > Thanks
>> >
>> > Phil
>> >
>> > --
>> > Distributed Computing stats
>> > http://stats.free-dc.org
>> >
>>
>>
>>
>> --
>> =A0- michael dykman
>> =A0- mdykman@gmail.com
>>
>> =A0May the Source be with you.
>
>
>
> --
> Distributed Computing stats
> http://stats.free-dc.org
>



--=20
=A0- michael dykman
=A0- mdykman@gmail.com

=A0May the Source be with you.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Trying to remove a filesort.

--00163641791b761d6b048fd8cf63
Content-Type: text/plain; charset=ISO-8859-1

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 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 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
> 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 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
> >> - mdykman@gmail.com
> >>
> >> May the Source be with you.
> >
> >
> >
> > --
> > Distributed Computing stats
> > http://stats.free-dc.org
> >
>
>
>
> --
> - michael dykman
> - mdykman@gmail.com
>
> May the Source be with you.
>



--
Distributed Computing stats
http://stats.free-dc.org

--00163641791b761d6b048fd8cf63--

Re: Trying to remove a filesort.

--001636416ae370c40f048fd8d9d8
Content-Type: text/plain; charset=ISO-8859-1

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 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 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 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
> > 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 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
> > >> - mdykman@gmail.com
> > >>
> > >> May the Source be with you.
> > >
> > >
> > >
> > > --
> > > Distributed Computing stats
> > > http://stats.free-dc.org
> > >
> >
> >
> >
> > --
> > - michael dykman
> > - mdykman@gmail.com
> >
> > May the Source be with you.
> >
>
>
>
> --
> Distributed Computing stats
> http://stats.free-dc.org
>

--001636416ae370c40f048fd8d9d8--

Re: Trying to remove a filesort.

--0016367659eb6dfb9b048fd90613
Content-Type: text/plain; charset=ISO-8859-1

It's in my.cnf. There is 12Gb in the database server and I watch it fairly
carefully and have not gone into swap yet in the past few years.

On Thu, Sep 9, 2010 at 3:43 PM, Ananda Kumar wrote:

> 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 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 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 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
>> > 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 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
>> > >> - mdykman@gmail.com
>> > >>
>> > >> May the Source be with you.
>> > >
>> > >
>> > >
>> > > --
>> > > Distributed Computing stats
>> > > http://stats.free-dc.org
>> > >
>> >
>> >
>> >
>> > --
>> > - michael dykman
>> > - mdykman@gmail.com
>> >
>> > May the Source be with you.
>> >
>>
>>
>>
>> --
>> Distributed Computing stats
>> http://stats.free-dc.org
>>
>
>


--
Distributed Computing stats
http://stats.free-dc.org

--0016367659eb6dfb9b048fd90613--

Re: Trying to remove a filesort.

--0016367f9a0ed3b991048fd930ca
Content-Type: text/plain; charset=ISO-8859-1

Its not advisiable...as this size will be allocated to all the session and
cause system running out of memory.
It should be set at session and in my.cnf it should be around 2 MB.

Please correct if i am wrong.

regards
anandkl

On Fri, Sep 10, 2010 at 1:26 AM, Phil wrote:

> It's in my.cnf. There is 12Gb in the database server and I watch it fairly
> carefully and have not gone into swap yet in the past few years.
>
> On Thu, Sep 9, 2010 at 3:43 PM, Ananda Kumar wrote:
>
> > 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 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
> 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 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
> >> > 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 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
> >> > >> - mdykman@gmail.com
> >> > >>
> >> > >> May the Source be with you.
> >> > >
> >> > >
> >> > >
> >> > > --
> >> > > Distributed Computing stats
> >> > > http://stats.free-dc.org
> >> > >
> >> >
> >> >
> >> >
> >> > --
> >> > - michael dykman
> >> > - mdykman@gmail.com
> >> >
> >> > May the Source be with you.
> >> >
> >>
> >>
> >>
> >> --
> >> Distributed Computing stats
> >> http://stats.free-dc.org
> >>
> >
> >
>
>
> --
> Distributed Computing stats
> http://stats.free-dc.org
>

--0016367f9a0ed3b991048fd930ca--

Re: Trying to remove a filesort.

You make an excellent point. If there are a lot of connections to
that server, many sort buffers may be in use and can squeeze ram out
of the rest of the system. 2M is a pretty good choice.

- md

On Thu, Sep 9, 2010 at 4:08 PM, Ananda Kumar wrote:
> Its not advisiable...as this size will be allocated to all the session an=
d
> cause system running out of memory.
> It should be set at session and in my.cnf it should be around 2 MB.
>
> Please correct if =A0i am wrong.
>
> regards
> anandkl
>
> On Fri, Sep 10, 2010 at 1:26 AM, Phil wrote:
>
>> It's in my.cnf. There is 12Gb in the database server and I watch it fair=
ly
>> carefully and have not gone into swap yet in the past few years.
>>
>> On Thu, Sep 9, 2010 at 3:43 PM, Ananda Kumar wrote:
>>
>> > 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 a=
nd
>> > paging will happen
>> >
>> > regards
>> > anandkl
>> >
>> > On Fri, Sep 10, 2010 at 1:10 AM, Phil 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 eit=
her
>> >> :(
>> >>
>> >> On Thu, Sep 9, 2010 at 3:16 PM, Michael Dykman
>> wrote:
>> >>
>> >> > How many rows before the GROUP BY? =A0Group by is, in effect a sort=
ing
>> >> > process.. =A0perhaps that contains enough data to justify going to =
disk.
>> >> >
>> >> > What is the value of the variable sort_buffer_size?
>> >> > =A0 =A0 =A0 =A0 show variables like '%sort%';
>> >> >
>> >> > =A0- md
>> >> >
>> >> > On Thu, Sep 9, 2010 at 3:04 PM, Phil 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 >
>> >> > wrote:
>> >> > >>
>> >> > >> The filesort is probably necessary because of the number of rows=
in
>> >> > >> the result set to be ordered. =A0How many rows do you get out of=
this
>> >> > >> query?
>> >> > >>
>> >> > >> =A0- michael dykman
>> >> > >>
>> >> > >> On Thu, Sep 9, 2010 at 1:53 PM, Phil wrot=
e:
>> >> > >> > I wonder if anyone could help with a query which I've been una=
ble
>> >> to
>> >> > >> > prevent
>> >> > >> > from using a filesort. Might be something obvious I'm
>> overlooking!
>> >> > >> >
>> >> > >> > I have a table which tracks milestones in distributed computin=
g
>> >> > 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 g=
oes
>> >> on.
>> >> > >> >
>> >> > >> > Now on one page for display I want to show the latest mileston=
e
>> 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 o=
n 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 temporar=
y; Using
>> >> > filesort
>> >> > >> > *************************** 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 so=
rt
>> in
>> >> > php
>> >> > >> > afterwards I guess but any other ideas?
>> >> > >> >
>> >> > >> > Thanks
>> >> > >> >
>> >> > >> > Phil
>> >> > >> >
>> >> > >> > --
>> >> > >> > Distributed Computing stats
>> >> > >> > http://stats.free-dc.org
>> >> > >> >
>> >> > >>
>> >> > >>
>> >> > >>
>> >> > >> --
>> >> > >> =A0- michael dykman
>> >> > >> =A0- mdykman@gmail.com
>> >> > >>
>> >> > >> =A0May the Source be with you.
>> >> > >
>> >> > >
>> >> > >
>> >> > > --
>> >> > > Distributed Computing stats
>> >> > > http://stats.free-dc.org
>> >> > >
>> >> >
>> >> >
>> >> >
>> >> > --
>> >> > =A0- michael dykman
>> >> > =A0- mdykman@gmail.com
>> >> >
>> >> > =A0May the Source be with you.
>> >> >
>> >>
>> >>
>> >>
>> >> --
>> >> Distributed Computing stats
>> >> http://stats.free-dc.org
>> >>
>> >
>> >
>>
>>
>> --
>> =A0Distributed Computing stats
>> http://stats.free-dc.org
>>
>



--=20
=A0- michael dykman
=A0- mdykman@gmail.com

=A0May the Source be with you.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

RE: Trying to remove a filesort.

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.bok@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=gcdmg-mysql-2@m.gmane.org

Re: Trying to remove a filesort.

--0016e6d7df7d1d234b048fdb6d11
Content-Type: text/plain; charset=ISO-8859-1

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 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.bok@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

--0016e6d7df7d1d234b048fdb6d11--