Add id (auto increment primary index) to existing table


I have an existing table with 2500 entries. I want to add a primary index (with auto_increment) to the table (labelled id). I tried adding it but it seemed to add it in a pretty random order. I’d like it to be added based upon the order of another column called ‘ref’. Is it possible to add a primary index in order of a column?


create a new table, do an INSERT SELECT from the old table, then delete the old table and rename the new one

Why not just make that field the primary index instead of adding an extra field.