how to reindex mysql table

Question

I have a table with many rows but they are out of order. Im using the field "id" as the primary key. I also have a "date" field which is a datetime field.

How could i reindex the table so that the entries are id'd in chronological order according to the date field

Answer 1

the way i would do it is to create a new table with auto increment index and just select all your old table into it ordering by date. you can then remove your old table.