6 posts in this topic

Posted

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.
[img]http://i.imgur.com/PQvoBf1.png[/img]

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

[img]http://i.imgur.com/q3oRu6O.png[/img]

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

Share this post


Link to post
Share on other sites

Posted

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.

Share this post


Link to post
Share on other sites

Posted

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

Share this post


Link to post
Share on other sites

Posted

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

Share this post


Link to post
Share on other sites

Posted

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.

Share this post


Link to post
Share on other sites

Posted

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 [b][u]defo [/u][/b]do some testing off production... as i know some takes can take 24hr + if bad sql is used...

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0

  • Recently Browsing   0 members

    No registered users viewing this page.