Optimize mySql for faster alter table add column
alter, mysql, performance
Solution
I faced a very similar situation in the past and i improve the performance of the operation in this way :
- Create a new table (using the structure of the current table) with the new column(s) included.
- execute a `INSERT INTO new_table (column1,..columnN) SELECT (column1,..columnN) FROM current_table;`
- rename the current table
- rename the new table using the name of the current table.
Problem
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?