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:
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
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
>>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?