Add id (auto increment primary index) to existing table

Databases
1

Hi!

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?

Cheers!

2

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

3

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