List:General Discussion« Previous MessageNext Message »
From:Jason Hamilton Date:May 23 2003 3:44pm
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 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:

I wanted to add that in addition to the index on c1.id, c2.id, and c1.size,
I also tried creating an index on c1.id, c1.size. The explain says it is
using the new index on id+size, but:

         c1 ref id,id_size id_size 2 const 34888 Using where; Using filesort
         c2 eq_ref PRIMARY,id PRIMARY 2 c1.id 1

Still using filesort on the results :( 

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