In the last episode (Oct 15), mos said: > At 07:47 AM 10/15/2003, you wrote: > >mos <[EMAIL PROTECTED]> 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
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
