How to quickly re-sort a MySQL table by one of the columns?

Question

What are the most efficient SQL queries that would take an existing MySQL table and re-sort it by one of the columns? Not a selection, but the whole table should be sorted by one column. Somehow using a temporary table, I guess, but what's the best way?

Answer 1

The best way is to keep your hands off the table structure and learn database basics.

Make an index on this column.

Answer 2

the table is just the data its not inherently sorted. a query obtaining the data from that table may be sorted, but the table itself is not.

Having indexes on the columns you want your queries to sort on will help give the best query time.

Answer 3

Tables aren't sorted*. Do not assume they contain data in any default order. This is what ORDER BY is for in your queries, even if there is no specific WHERE clause filter.

Answer 4

The easiest thing is to add a key to that column.

ALTER TABLE table ADD KEY (column);

Then you can use EXPLAIN SELECT to show that you're using the key for the query.

If the table is constantly having rows added and removed, you can also run OPTIMIZE TABLE table for good measure.

Answer 5

Even though you really shouldn't need to do it, you can use ALTER TABLE ... ORDER BY ... to reorder your table. But think twice before doing it. Then think a few more times. Then don't do it.

Answer 6

My 0.02c; maybe he just wants to reorder his table in a more readable way. Imagine a table like:

 TABLE FOO with ID Char(3) Primary Key,DESCRIPTION Varchar

with 4 rows

ID  | DESCRIPTION
001 | Test1
004 | Test4
003 | Test3
002 | Test2

It could be that rows 003 and 002 were inserted later (not known when table was created), so OP would like to reorder records in a more readable way (i know he could select with order clause).

In this case i would backup the table (SELECT * INTO backup_FOO FROM FOO) , remove every constraints, truncate it and reload it ordered by OP preferred column.