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
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
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
10 matches
Mail list logo
