performance - Optimize mySql for faster alter table add column

Question

I have a table that has 170,002,225 rows with about 35 columns and two indexes. I want to add a column. The alter table command took about 10 hours. Neither the processor seemed busy during that time nor were there excessive IO waits. This is on a 4 way high performance box with tons of memory.

Is this the best I can do? Is there something I can look at to optimize the add column in tuning of the db?

Answer 1

I faced a very similar situation in the past and i improve the performance of the operation in this way :

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 (column1,..columnN) SELECT (column1,..columnN) FROM current_table;
3. rename the current table
4. rename the new table using the name of the current table.