15

What would be the fastest way to add a new column in a large MySQL table?

ALTER TABLE ADD COLUMN creates a copy of the full table, and then replaces the old one with the new create table. While this process is running, the original table is readable, but all inserts and updates are stalled.

On large tables the copy can take a long time, is there any way to reduce it?

informatik01
  • 16,038
  • 10
  • 74
  • 104
Maraino
  • 203
  • 3
  • 6
  • Related question: http://stackoverflow.com/questions/1645215/how-do-i-add-a-column-to-large-sql-server-table – B T May 19 '11 at 18:38
  • 1
    Consider tuning innodb_log_file_size (But be careful, see http://www.mysqlperformanceblog.com/2011/07/09/how-to-change-innodb_log_file_size-safely/) and innodb_log_buffer_size. For more information, see my answer here: http://stackoverflow.com/a/12688184/1148030 – Peter Lamberg Oct 02 '12 at 10:07

3 Answers3

5

You are stuck doing the ALTER TABLE. The best possible way to effectively deal with this, is to use a MASTER-MASTER setup.

You can modify MASTER1 first, and just use MASTER2 in production. Then you switch over and do the exact opposite.

Evert
  • 93,428
  • 18
  • 118
  • 189
2

Don't do this live on an active system. For an active system, do this while you take the system down for regular maintenance.

Paul Sonier
  • 38,903
  • 3
  • 77
  • 117
  • It's possible to do the update in a slave box, but is there any non-manual way to recreate the updates on the real one ? the binary log or something like that. The manual way would be log every insert or update and the apply in the same order. – Maraino Jun 18 '09 at 22:38
1

Assuming an INNODB table:

  1. Copy {Existing Table} to {New Table} with no data (schema only)
  2. Use Alter table commands to add/alter columns on NEW table (should work in a flash)
  3. MYSQLDUMP existing table (using single-transaction, --no-drop-table, etc) to file
  4. import dump file into {New Table} (this, may take awhile, but old table still usable during this period)
  5. during maintenance period, DROP {Old Table} and rename {New Table} to previous {Old Table}.
VisionQuest
  • 151
  • 1
  • 3