2

I've got a 100 million row table and it's taking hours to add a column, let alone run a more complicated query. How can I speed this up?

Trying to run this:

Alter Table diablo add NGAGE_URN varchar(100) FIRST;
Dharman
  • 30,962
  • 25
  • 85
  • 135
Lucas
  • 401
  • 1
  • 8
  • 20

3 Answers3

2

Use pt-online-schema-change or the manual method I described earlier.

Community
  • 1
  • 1
bishop
  • 37,830
  • 11
  • 104
  • 139
1

Mysql is notorious for being slow when adding columns. I have seen it take 6 hours on only a few hundred million rows. The bigger problem is that during the alteration, the table is exclusively locked, so you're off-line while it happens.

Create a new table, steadily fill it with the old data. When you're almost up to date, shut down your apps, copy the remaining rows, rename the tables so the new table has the old name, then you're good to go. Downtime: a couple of minutes.

You'll have to deal with updates that occurred after you started moving data, so a timestamp or similar will help there.

Bohemian
  • 412,405
  • 93
  • 575
  • 722
0

create a new table with the column. Insert all the records from the old table into it. Drop the old table. rename the new one to the correct name.

  • you could run into problems depending on referential integrity and constraints. Im sure MySQL has an option to turn of checks on constraints while you do it though. –  Jan 31 '14 at 01:54