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. > > 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
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
