List:General Discussion« Previous MessageNext Message »
From:Egor Egorov Date:May 24 2003 2:05pm
Subject:Re: re: MySQL using filesort :(
View as plain text  
Jason Hamilton <jason@stripped> wrote:
> At 11:36 AM 5/23/2003, Egor Egorov wrote:
>>On Friday 23 May 2003 18:11, Jason Hamilton wrote:
>> > 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:
>>
>>Do you have separate indexes on c1.id and c1.size or multi-column index on
>>(c1.id, c1.size) ?
> 
> I have a index on c1.id, AND c1.size. I also have a multi col index on 
> c1.id,c1.size.
> I also tried the query without the LEFT JOIN, with the same result - using
> filesort. 

Please show me the output of SHOW CREATE TABLE and the exactly output of EXPLAIN SELECT.



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___     ___ ____  __
  /  |/  /_ __/ __/ __ \/ /    Egor Egorov
 / /|_/ / // /\ \/ /_/ / /__   Egor.Egorov@stripped
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
       <___/   www.mysql.com



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