Mysql – What’s a good way to add a PRIMARY KEY to a large InnoDB table

innodbMySQLprimary-key

I have a table with around 30 million rows in MySQL 5.5 using the InnoDB storage engine. This table has a foreign key that links to another table with around 3 million rows.

I want to add a primary key to the larger table, but am unsure of how best to go about this. There's no existing column (or set of columns) I can use for this, so how should I go about this?

I have seen some references to an internal InnoDB record ID, but have not found if this is accessible.

Otherwise, perhaps I will need to dump and reload the data, adding the id in manually.

Thanks.

Best Answer

this may be the wrong way to go about it, but..

couldn't you just alter the table structure,

add a primary key column to the table

then...

write a quick script to go through the entire table

adding an autoincrementing value to the new column?

Related Question