Bug #2078 Suggestion: OPTIMIZE TABLE
Submitted: 10 Dec 2003 8:05 Modified: 10 Dec 2003 10:27
Reporter: Mike MySQLAB Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Server: MyISAM storage engine Severity:S4 (Feature request)
Version: OS:
Assigned to: CPU Architecture:Any

[10 Dec 2003 8:05] Mike MySQLAB 
Description:
Here is a suggestion that will help to speed up Select queries. A lot of the time tables are read-only and use the same sort order, like on a web page.
It would be nice if the OPTIMIZE table command could have a sort order to physically sort the table in a default order. 

This means subsequent Select statements that do not have an Order By clause will display the rows in this optimized order. This eliminates the need for sorting the rows every time a select statement is issued. (This assumes no table updates were made after the Optimize command was run.)

Select queries that don't have to be sorted will run faster than sorted queries and will improve performance considerably.

How to repeat:
n/a
[10 Dec 2003 8:18] Dean Ellis 
Do you mean something in addition to ALTER TABLE tablename ORDER BY colname;?
[10 Dec 2003 10:27] Dean Ellis 
Alright.

Just a note that you should be aware that depending on the physical order of the rows is generally a bad thing to do.  If you need a specific order you should be using an ORDER BY clause.

Thank you