Preordering the table by a specified column

• There is no natural, implied or intrinsic ordering to a table
• Any order is only guaranteed by the outermost ORDER BY

If this is slow, then you can either

• make the index covering (a UNIQUE constraint is an index already)
• make it the clustered index (which is covering by its nature)

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.

Note : In theory, you may see a very, very tiny performance difference in an InnoDB table (call it mytable) if you do the following:

CREATE TABLE mytable_new LIKE mytable;
INSERT INTO mytable_new
SELECT * FROM mytable
ORDER BY {primary-key columns};
ALTER TABLE mytable RENAME mytable_old;
ALTER TABLE mytable_new RENAME mytable;

This would order the gen_clust_index (internal cluster index) in both rowid order and primary key order. However, performing this internal ordering of rowids is like turning lead into gold. The time, effort, and energy processing this far outweighs any performamce benefits this may seemingly buy but never fulling realizing since each primary key lookup results in a rowid lookup as well. Long story short : don't do this unless you are using MyISAM only.

Here is my criteria for using ORDER BY against the column in a PRIMARY KEY

• 4 or more columns in the PRIMARY KEY
• For a PRIMARY KEY with n columns, columns 1 .. n-1 should be static in your WHERE and GROUP BY clauses, whereas column n would serve as the range column in your queries
• Use MyISAM storage engine only !!!

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.