MySQL Change primary on a large table

[SuperKid]

Hi,

I need some advice and help, I need to change the primary key ID to auto increment on a table which is 19.4GB in size with approximately 150 million entries.

Apparently running Alter table is going to take hours and hours to complete which I can understand.

The table has huge amounts of data in it

What would be the fastest way to update the items table?

[Phouchg]

There isn't. In MySQL/Maria nearly any fumbling with ALTER will want to copy all data, especially in this case, because it's primary key which is being changed - reindex of the whole pile of crap is required.

[tim_s]

The issue is the amount of time it will take to re-build the tables?

[Kami-]

You change the index, you've got to rebuild the indicies, good job with that not taking hours ;)

[kjordan2001]

Sometimes it ends up being faster to do a dump, recreate the table, and then reimport the data. MySQL is definitely never fast at altering even medium size tables.

[Seabizkit]

yeah i suggest coping this to a non production server for some testing....

On the test machine:

I would backup the table and rename it, then create the new table with the old name.

then do insert or something like that... I've heard its MUCH quicker to delete then import.... an to alter the records.

with that amount of data i would defo do some testing off production... as i know some takes can take 24hr + if bad sql is used...