It is possible to perform ORDER BY on a table.
ALTER TABLE ... ORDER BY {columns} ;
According to the MySQL Documentation
ORDER BY enables you to create the new table with the rows in a specific order. Note that the table does not remain in this order after inserts and deletes. This option is useful primarily when you know that you are mostly to query the rows in a certain order most of the time. By using this option after major changes to the table, you might be able to get higher performance. In some cases, it might make sorting easier for MySQL if the table is in order by the column that you want to order it by later.
ORDER BY syntax permits one or more column names to be specified for sorting, each of which optionally can be followed by ASC or DESC to indicate ascending or descending sort order, respectively. The default is ascending order. Only column names are permitted as sort criteria; arbitrary expressions are not permitted.
ORDER BY does not make sense for InnoDB tables that contain a user-defined clustered index (PRIMARY KEY or NOT NULL UNIQUE index). InnoDB always orders table rows according to such an index if one is present.
Note : When used on a partitioned table, ALTER TABLE ... ORDER BY orders rows within each partition only.
Since InnoDB performs a double index lookup (One against the PRIMARY KEY and then one against the internal clustered index gen_clust_index), running ORDER BY on an InnoDB table really buys you nothing in performance gains.
IMHO, if the PRIMARY KEY has something obnoxious like 8 columns
- For MyISAM, you will have marvelous performance for lookups and range searches
- For InnoDB, you will only have extremely marginal results at best, if any.
CAVEAT
The reason I mentioned an 8-column PRIMARY KEY? My past employer had dozens of GB of read-only data sent monthly from an outside vendor. I learned of ALTER TABLE...ORDER BY while there and used it against those MyISAM tables. Since a lot of range queries were done against the PRIMARY KEY, the query time was astounding to everyone, including myself.