List:General Discussion« Previous MessageNext Message »
From:mos Date:October 15 2003 2:40pm
Subject:Re: Select always "Using FileSort"
View as plain text  
At 07:47 AM 10/15/2003, you wrote:
>mos <mos99@stripped> wrote:
> > I want to speed up a large query and I noticed if I do an:
> >
> > Explain select * from LargeTable order by IndexCol1
> >
> > it always has Extra "Using FileSort", which means it is physically sorting
> > the result set.
> > I've tried different tables and sorted on different index columns, and it
> > is always using FileSort.
> >
> > How do I get MySQL 4.1.0 to use an index when sorting a MyISAM table?
> > Or is Explain broken and does it always report it is using FileSort?
>
>Look at the last paragraph of the following section in the manual:
>         http://www.mysql.com/doc/en/MySQL_indexes.html


Egor,

You mean the paragraph:
 >>If the use of the index would require MySQL to access more than 30% of 
the rows in the table. (In this case a table scan is probably much faster, 
as this will require us to do much fewer seeks.) Note that if such a query 
uses LIMIT to only retrieve part of the rows, MySQL will use an index 
anyway, as it can much more quickly find the few rows to return in the 
result.<<

Sure, but doesn't that apply to Where clauses only?

The "How MySQL Optimizes Order By" 
http://www.mysql.com/doc/en/ORDER_BY_optimisation.html has examples similar 
to what I'm trying to accomplish

 >>The following queries will use the index to resolve the ORDER BY / GROUP 
BY part:
 >>SELECT * FROM t1 ORDER BY key_part1,key_part2,...

None of my very simple queries (even without Where clauses) will ever use 
the index for sorting even when those order by columns are part of the 
index or the entire index. Explain always says "FileSort".

Have you (or anyone else) tried a simple query on your indexed table to see 
if you ever see Explain not use FileSort?

TIA
Mike


Thread
Select always "Using FileSort"mos15 Oct
  • Re: Select always "Using FileSort"Egor Egorov15 Oct
    • Re: Select always "Using FileSort"mos15 Oct
      • Re: Select always "Using FileSort"Dan Nelson15 Oct
Re: Select always "Using FileSort"Prasad Budim Ram15 Oct
Re: Select always "Using FileSort"mos16 Oct