List:General Discussion« Previous MessageNext Message »
From:Jason Hamilton Date:May 23 2003 3:11pm
Subject:re: MySQL using filesort :(
View as plain text  
At 11:01 AM 5/23/2003, Egor Egorov wrote:
> > I'm having a problem with MySQL and ORDER BY.
> >
> > With this statement, extra = "Using where"
> >
> > EXPLAIN SELECT * FROM c1 LEFT JOIN c2 ON c1.id = c2.id WHERE c1.id = '21'
> >
> > Simply adding "ORDER BY c1.size DESC" changes the extra
> > to "Using where; Using filesort".
> >
> > Naturally I have an index on c1.id (c2.id also has an index), and
> > c1.size.
> >
> > I'm at a loss as to why it's going to filesort. The query speed
> > is quick on most results, except for when there is > ~10,000 matches,
> > then it can take several seconds to process. Most matches are
> > under 1,000 results, though some can be as high as 150,000.
> >
> > Is there anything I can do to remove use of filesort, or increase
> > the speed of the query on large matches?
>
>What version do you use? 3.23.xx ? If so, you can upgrade to 4.0.x. In 4.0.x
>MySQL can use index in ORDER BY .. DESC queries.


Egor,

I'm running mysql-standard-4.0.12. The machine is a 2.4ghz server w/
1 gig of ram. I have been screwing around with the conf settings, heres
some of the settings:

set-variable    = query_cache_type=1
set-variable    = query_cache_size=2M
set-variable    = max_connections=512
set-variable    = low-priority-updates=ON
set-variable    = key_buffer=256M
set-variable    = max_allowed_packet=1M
set-variable    = table_cache=512
set-variable    = sort_buffer=5M
set-variable    = read_rnd_buffer=5M
set-variable    = record_buffer=3M
set-variable    = myisam_sort_buffer_size=64M
set-variable    = thread_cache=8
set-variable    = thread_concurrency=2

Any help is appreciated.

Jason 

Thread
Minimum Requirement to run a MySQL test setupNitin Nanivadekar23 May
  • Re: Minimum Requirement to run a MySQL test setupDan Nelson23 May
  • MySQL using filesort :(Jason Hamilton23 May
    • re: MySQL using filesort :(Egor Egorov23 May
      • re: MySQL using filesort :(Jason Hamilton23 May
        • re: MySQL using filesort :(Jason Hamilton23 May
        • re: MySQL using filesort :(Egor Egorov23 May
          • re: MySQL using filesort :(Jason Hamilton23 May
            • Re: re: MySQL using filesort :(Egor Egorov24 May
              • Defaulting fulltext search to 'AND'?Jason Hamilton24 May
                • Re: Defaulting fulltext search to 'AND'?Victoria Reznichenko26 May
              • Re: re: MySQL using filesort :(Egor Egorov26 May
RE: Minimum Requirement to run a MySQL test setupMike Hillyer23 May