Default sort-ordering in MySQL (ALTER TABLE ... ORDER BY ...;)

Question

Assume that the default ordering of a MySQL-table (ISAM) is changed by executing:

ALTER TABLE tablename ORDER BY columnname ASC;

From now on, am I guaranteed to obtain records retrieved from the table in the order of "columnname ASC" assuming no "ORDER BY" is specified in my queries (i.e. "SELECT * FROM tablename WHERE ... LIMIT 10;")?

Are there any corner-cases that I should be aware of?

Update #1: Thanks a lot to Quassnoi who correctly pointed out that INSERTs and DELETEs messes up the ordering. This leads me to the following to extra questions:

• What about UPDATEs? Assume that no INSERTs or DELETEs are made to the table, but only updates - will the sort order be intact?
• Assume that INSERTs and DELETEs are made - how do I "rebuild" the sorting again, say once a day (in this specific case the table only changes daily, so rebuilding it daily after the changes are done should still be OK!). Does REPAIR TABLE fix it, or must add do ALTER TABLE ... ORDER BY again?

Answer 1

From documentation:

Note that the table does not remain in this order after inserts and deletes

Actually, if you issue SELECT ... ORDER BY to this table, the option to ALTER TABLE won't spare you of filesort, but instead make filesort much faster.

Sorting an already ordered set is equivalent to browsing this set to ensure everything is OK.

What about UPDATEs? Assume that no INSERTs or DELETEs are made to the table, but only updates - will the sort order be intact?

If your table does not contain any dynamic fields (like VARCHAR or 'BLOB'), then most probably MyISAM will not move it when updating.

I would not rely on this behavior, though, if I were building a nuclear power plant or something I get paid for.

Assume that INSERTs and DELETEs are made - how do I "rebuild" the sorting again, say once a day (in this specific case the table only changes daily, so rebuilding it daily after the changes are done should still be OK!). Does REPAIR TABLE fix it, or must add do ALTER TABLE ... ORDER BY again?

You'll need to do ALTER TABLE ... ORDER BY.

REPAIR just fixes the physical structure of a corrupted table.

Answer 2

Physically ordering a column can save loads of IO. It's perfectly legitimate method used in advanced systems to speed query time. You just need to reorg the data every now and then so its stays clustered. Just because some folks haven't heard of it doesn't mean it doesn't exist. - 25 year advanced DB design veteran.