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?
-
Can you clarify what you mean by 'sorting a table' but not performing a SELECT. Are you saying you want to 'sort' it in place? If so, what are you trying to achieve/improve by doing that? – Dancrumb Mar 29 '10 at 14:56
-
1Yes, sort in place, somehow. Or in a temporary table, it does not matter. I would like to do this on a pretty static table (imported data) so that I don't have to use "ORDER BY" in later selects. – Vacilando Mar 29 '10 at 15:31
6 Answers
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.

- 776,304
- 153
- 1,341
- 1,358
-
While I don't want to rant on politics I think there is a valid reason for doing this, convenience. Think about the repr() in python, technically ALL objects should be represented consistently by strings like
for a datetime object in this example. Do you want to know the actual date? You can call .strftime() on it whenever you want, but it's convenient that repr() tells it without explicit formatting. – rgz Apr 19 '11 at 21:00
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.

- 41,484
- 20
- 104
- 125
-
Tables can be sorted in SQL Server and PostgreSQL, just CLUSTER them on a index and you have a sorted table. – Frank Heikens Mar 29 '10 at 15:25
-
1Well, if I do "SELECT column FROM table", I retrieve the results in the order they are written in the table (I assume always in the same order?!) But in my application I need them to be sorted. But when I do "SELECT column FROM table ORDER BY column" it is a very heavy operation. Because the table is static (regularly imported data from elsewhere), I would like to sort the table by column once and then my selections would be fast and sorted without using "ORDER BY". That's the idea anyway. – Vacilando Mar 29 '10 at 15:29
-
There is no way to guarantee anything about the "order they are written to the table". If your table uses InnoDB, there's some limited clustered index support (which defines the order the data is stored); for MyISAM this is not possible. – Joe Mar 29 '10 at 15:49
The best way is to keep your hands off the table structure and learn database basics.
Make an index on this column.

- 156,878
- 40
- 214
- 345
-
It goes without saying the table is indexed; in fact I am sorting on the int(10) column that has the primary index. – Vacilando Mar 29 '10 at 15:39
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.

- 32,535
- 13
- 101
- 181
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.

- 71,966
- 47
- 171
- 241
-
whilst this might be true, why not just view the table with a query with an order? What does reordering the table give you over that? – Sam Holder Mar 29 '10 at 15:19
-
I'm just speculating here :): you don't have to specify "order by" everytime you make a select and, speculating again,a partially ordered table could be misleading for the reader who does not know that some missing IDs are at the bottom of the table. – systempuntoout Mar 29 '10 at 15:27
-
1"Why not just view the table with a query with an order?" As systempuntoout guessed, it is precisely because if the table were ordered by ID then subsequent selects would not need to use "ORDER BY" and they would be quicker. (Again, the table in the example does not change dynamically.) – Vacilando Mar 31 '10 at 10:23
-
@Vacilando So, why don't you just backup and reload the table as i said? – systempuntoout Mar 31 '10 at 10:56
-
1@Vacilando I was the only one who undestood your problem,i've upvoted your question, i got 0 point and you marked another answer Valid.Sad life :))). – systempuntoout Mar 31 '10 at 10:57
-
I marked Sam Holder's answer above correct because he seems to say there is no way to have an inherently sorted table; that records must be sorted during selection. Correct me, anybody, if I did not understand Sam's answer correctly. – Vacilando Apr 01 '10 at 14:58
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.

- 73
- 5