I have a table and I am always getting the data by ordering a column. In my case, I order by datenum. However, the selection is so slow because of the ordering.
Is it possible to order the table after datenum. This column is defined as unique. If defined as an index, would the selection will be faster?
If this is slow, then you can either
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
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
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.
