List:General Discussion« Previous MessageNext Message »
From:mos Date:October 16 2003 3:51pm
Subject:Re: Select always "Using FileSort"
View as plain text  
At 10:10 AM 10/15/2003, you wrote:
>In the last episode (Oct 15), mos said:
> > 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.
> >
> > 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?
>
>It shouldn't.  When you are iterating over an entire table, it's almost
>always faster to filesort the entire table as a single unit (which can
>be done with one sequential pass over the table) then it is to walk an
>index and do random record pulls from the table (which will involve one
>disk seek per record).

Here's what I discovered. If all of the display columns in the Select 
statement are contained in the index that used in the Order By clause, then 
MySQL will use the index for sorting. If just one column in the display 
columns are NOT in the Order By index, then it uses FileSort.

This makes sense because if MySQL can get away with reading just the index 
to get all the columns for both the display and sort, then it uses the 
index as the sort.
If it has to read a field from the data record for displaying a column, 
then it uses FileSort and doesn't bother with the index for sorting.
(It would be nice if the manual explained this).

>Note that filesort doesn't really mean "use
>files to sort this"; a small table may be sorted completely in memory.

My table is 2.5 million rows, and the sort is on 6 columns, so it will end 
up using the hard drive for sorting.
Since the temp drive can now span several drives, I'll try adding a RAM 
disk for the first drive and that should speed up the sorting. If it needs 
more disk space then it will overflow to the conventional hard disk.

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