Jump to content



Photo

MySQL Change primary on a large table

primary key mysql mariadb

  • Please log in to reply
5 replies to this topic

#1 SuperKid

SuperKid

    Im no superman

  • Joined: 21-April 08
  • Location: Birmingham, England, UK
  • OS: OS X 10.8, iOS 7
  • Phone: iPhone 4S

Posted 19 February 2013 - 15:21

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

Phouchg

    Random Oracle

  • Tech Issues Solved: 9
  • Joined: 28-March 11
  • Location: Tannhäuser Gate
  • OS: V'Ger 6.1.7601 x64

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

tim_s

    Default

  • Joined: 07-January 13
  • OS: OSX (Macbook Pro i7), Windows 7 (Gaming), Gentoo
  • Phone: Samsung Galaxy SIII, iPhone 4s

Posted 19 February 2013 - 20:46

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

#4 Kami-

Kami-

    ♫ d(-_-)b ♫

  • Tech Issues Solved: 2
  • 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

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

Seabizkit

    Neowinian

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



Click here to login or here to register to remove this ad, it's free!