Jump to content


MySQL Change primary on a large table

primary key mysql mariadb

  • Please log in to reply
5 replies to this topic

#1 SuperKid


    Im no superman

  • Joined: 21-April 08
  • OS: Windows 8.1, OS X 10.10, iOS 8
  • Phone: iPhone 6 Plus

Posted 19 February 2013 - 15:21


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.
Posted Image

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

Posted Image

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

#2 Phouchg


    has stopped responding

  • Tech Issues Solved: 9
  • Joined: 28-March 11

Posted 19 February 2013 - 19:25

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.

#3 tim_s



  • Tech Issues Solved: 1
  • Joined: 07-January 13
  • OS: OSX (Macbook Pro i7), Windows 7 (Gaming), Gentoo
  • Phone: iPhone 5s

Posted 19 February 2013 - 20:46

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

#4 Kami-


    ♫ d(-_-)b ♫

  • Tech Issues Solved: 3
  • Joined: 28-July 08
  • Location: SandBox

Posted 20 February 2013 - 12:28

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

#5 kjordan2001


    Mystery Solver

  • Tech Issues Solved: 1
  • Joined: 27-May 02

Posted 21 February 2013 - 22:05

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.

#6 Seabizkit



  • Joined: 28-February 12

Posted 21 February 2013 - 22:26

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...