List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:October 15 2003 3:10pm
Subject:Re: Select always "Using FileSort"
View as plain text  
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).  Note that filesort doesn't really mean "use
files to sort this"; a small table may be sorted completely in memory.

-- 
	Dan Nelson
	dnelson@stripped
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