faster alter table add column

 

1. Create a new table (using the structure of the current table) with the new column(s) included.
2. execute a INSERT INTO new_table SELECT (column1,..columnN) FROM current_table;
3. rename the current table
4. rename the new table using the name of the current table.

 

1. CREATE TABLE new_table LIKE table;

2. INSERT INTO new_table SELECT * FROM table;

3&4. RENAME TABLE table = old_table, table = new_table;

 

The usual trick for loading MyISAM table efficiently is to disable keys, load the data and renalbe the keys:

mysql> ALTER TABLE test.load_data DISABLE KEYS;
-- load data
mysql> ALTER TABLE test.load_data ENABLE KEYS;
dropped all indexes -- then added the field  and recreate indexes

 

REF:

http://stackoverflow.com/questions/5677932/optimize-mysql-for-faster-alter-table-add-column

http://dba.stackexchange.com/questions/9746/mysql-fastest-way-to-alter-table-for-innodb

http://dba.stackexchange.com/questions/134269/fastest-way-to-add-new-column-in-mysql