Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
I like this question because it introduces the idea of performing the ORDER BY on a table. +1 !!! –  RolandoMySQLDBA Aug 29 '11 at 12:42
add comment

2 Answers

up vote 5 down vote accepted
  • 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)
share|improve this answer
add comment

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.

share|improve this answer
    
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 '11 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. –  RolandoMySQLDBA Aug 29 '11 at 14:46
    
Thanks I'm still picking up the finer points of MySQL –  gbn Aug 29 '11 at 15:11
    
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 '11 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... –  RolandoMySQLDBA Sep 1 '11 at 16:41
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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