Things you manage to miss - ALTER TABLE .. ORDER BY
By Lachlan Mulcahy on Dec 04, 2009
It's funny how you can use a product heavily for so many years and still be completely unaware of some features.
Today a customer mentioned reordering a MyISAM table to get rid of fragmentation. I asked them to clarify what they meant by reordering, assuming that they were running an OPTIMIZE or a NULL ALTER TABLE operation and they said that they use ALTER TABLT t1 ORDER BY f1, f2, f3,..etc;
To my surprise checking out the syntax reference on the ALTER TABLE manual page, there it was in black and white. You can make the server rebuild your table and order the rows by your own sort criteria. This could be helpful in situations where you commonly read rows in a certain sort order because it will allow sequential disk reads rather than random seeks which can be much more expensive. Also, as a side effect of rebuilding the table you will do away with any fragmentation that exists.
Note: This is really only relevant for MEMORY or MyISAM tables. InnoDB tables will always be ordered by PRIMARY KEY. Also, the table will not be maintained in the order you specify, so changes to the table can cause your precious ordering to be messed up -- it might be more suitable for tables with somewhat static data.
In reality this is the sort of thing you'd really need to benchmark to see if it equates to any real performance increase for your application, but it is at least something to keep up your sleeve.
It just goes to show, you never stop learning!...