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,
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
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?
-
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
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
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
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 anan...@gmail.com wrote:
have u set sort_buffer_size at session level or in my.cnf.
Setting high value in
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 freedc@gmail.com wrote:
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 anan...@gmail.com wrote:
Its not advisiable...as
-
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
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
11 matches
Mail list logo
