Help-How to speed up "order table table-name order by col-name" for large table???

[jun zhao]

Hi all,
I have a table with 2 column: id and name. Column name is of type varchar(100).
The table has about 0.1 billion rows, taking 11G disk space.

Now what I want to do is alter the table by the name column. I ran the command: "order table table-name order by name"

1).Firstly, it create tmp table,which takes 19G disk space.
2).After that,the 19G space seems to be released(I see the free disk space increase
by "df").
3).Then I see that in the mysql directory, the size of the temp file #sql-14f7-2.MYD" increases 2M every time, but very slow. I guess this temp file is the new table after order. But how can it be so slow? It only increase 2M every 2 minutes or so, which would takes weeks to finish the command.

So could any one tell me how can I speed up this command? Are there any variables I can adjust to make this faster?

BTW, my setup is: 3G ram, 100G disk space. And I use Mysql 5.0.27.


Thanks a lot for any comments!


-Qian