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

by

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.

More Related Contents:

  1. How to find similar results and sort by similarity?
  2. Mysql: Select rows from a table that are not in another
  3. add column to mysql table if it does not exist
  4. Sorting varchar field numerically in MySQL
  5. Mysql order by specific ID values
  6. MySQL: ALTER TABLE if column not exists
  7. Adding multiple columns AFTER a specific column in MySQL
  8. How can I modify the size of column in a MySQL table?
  9. MySQL: Sort GROUP_CONCAT values
  10. Error renaming a column in MySQL
  11. ALTER TABLE to add a composite primary key
  12. How to add not null constraint to existing column in MySQL
  13. Alter MySQL table to add comments on columns
  14. ORDER BY date and time BEFORE GROUP BY name in mysql
  15. updating columns with a sequence number mysql
  16. Create an index on a huge MySQL production table without table locking
  17. MySQL: ALTER IGNORE TABLE ADD UNIQUE, what will be truncated?
  18. ALTER table – adding AUTOINCREMENT in MySQL
  19. mysql sorting of version numbers
  20. How to change MySQL column definition?
  21. Alter table if exists or create if doesn’t
  22. is php sort better than mysql “order by”?
  23. mysql custom sort
  24. `MODIFY COLUMN` vs `CHANGE COLUMN`
  25. ORDER BY alphabet first then follow by number
  26. How do I add more members to my ENUM-type column in MySQL?
  27. Optimizing MySQL for ALTER TABLE of InnoDB
  28. Remove Primary Key in MySQL
  29. MySQL CURRENT_TIMESTAMP on create and on update
  30. MySQL select all rows from last month until (now() – 1 month), for comparative purposes

Leave a Comment