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?

  • I like this question because it introduces the idea of performing the ORDER BY on a table. +1 !!! Aug 29, 2011 at 12:42

2 Answers 2

  • 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 !!!


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.

  • To clarify, assuming we stick to the rule "every table needs a clustered index" and we use InnoDB, this ORDER BY won't work. Only ISAM and heaps until the first write?
    – gbn
    Aug 29, 2011 at 12:50
  • @gbn - You are absolutely correct on InnoDB. I was just making a broader point for those interested that use MyISAM that could benefit from ORDER BY. In fact, +1 for your answer since it hits closer to home for users of InnoDB. In fact, I will add to the criteria to specifically use MyISAM. Aug 29, 2011 at 14:46
  • One more question: on InnoDB, is this useful when you want to add a surrogate key to a table maintaining row for row over "old" and "new" keys? So ORDER BY PoorWideOldKey gives rows in the same order as ORDER B ShinyNewNarrowKey ta
    – gbn
    Sep 1, 2011 at 15:56
  • Any key you create always has an additional key back to gen_clust_index attached. Shiny new would perform just as well as poor old. You could run ALTER TABLE ... ORDER BY ShinyNewKey columns to make the index pages contiguous but gen_clust_index also accessed each time back and forth with ShinyNewKey. You would have to reload table in ShinyNewKey Order for marginal results. The fewer and smaller the keys in ShinyNewKey index, the better. Try it out and let me know if table reload works for you... Sep 1, 2011 at 16:41
  • I couldn't figure out why my InnoDB table was not ordered by its primary key until I saw this answer. I thought it was called a "composite index" rather than "clustered index," but either way I now see that the table is ordered by my 2nd column, which is part of such an index (along with column 3). Feb 11, 2014 at 7:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.