Generate (i.e. write) a row number index column in MySQL

I need a row number index in my MySQL tables, based on ORDERing on a table column. I know about the

SELECT ... @curRow := @curRow + 1 AS row_number ...

trick which will output the row number; what I 开发者_如何学运维actually want, however, is for that row number to be written into the table as a persistent column. Performance is a critical issue. What is the best way of accomplishing this?

A tip from a friend saved the day. This seems to be the fastest way to accomplish the task:

1. Create a new table with identical column structure as the original one with the addition of an auto-increment column.
2. Add the data from the original table to the new one using INSERT INTO ... SELECT ... ORDER BY
3. Delete the original table

Did 9 million rows in 7 seconds.