0

I currently facing one problem when doing the migration especially adding new field to existing table which contains millions of records will take very long duration to complete. So I wonder is there any special trick way to make it migrate fast?

pang
  • 3,964
  • 8
  • 36
  • 42

1 Answers1

0

To do this without locking the table, what you can do is create a new table with the correct columns, select over the data from the old table, rename old table, rename new table, drop "old renamed table"

Some like this;

CREATE TABLE `table_new` LIKE `table`;
ALTER TABLE `table_new` ADD COLUMN ...;
ALTER TABLE `table_new` ADD COLUMN ...;
ALTER TABLE `table_new` ADD COLUMN ...;
INSERT INTO `table_new` SELECT x, y, z, 'newcol', 'newcol2', 'newcol3' FROM `table`
RENAME TABLE `table` TO `table_old`;
RENAME TABLE `table_new` TO `table`;
-- DROP TABLE `table_old` -- Might just leave this out for now, who knows...

Another thing to think about could be indexes, turn it off for the insert and re-enable them after the insert, this way they do not have to be recalculated all the time.

hank
  • 3,748
  • 1
  • 24
  • 37
  • if we do like this the time to insert million records to the new tables will take much time as well, maybe more than adding migrate field only. isn't it? – pang Mar 27 '13 at 07:38
  • No, doing this, preferably in a transaction, will be much faster than adding columns to a large table. Instead of having to allocate space for the new columns for every row, space will be allocated from scratch. – hank Mar 27 '13 at 07:42
  • 1
    I just remembered this old gem from facebook mysql team, https://www.facebook.com/note.php?note_id=430801045932 It's a good read – hank Mar 27 '13 at 07:45
  • @pang did you have a chance to try it yet? – hank Mar 27 '13 at 19:46