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:
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.