Optimize mySql for faster alter table add column

mysql performance alter
Andrew picture Andrew · Apr 15, 2011 · Viewed 35k times · Source

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

RRUZ picture RRUZ · Apr 15, 2011

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.

Related questions

ALTER TABLE ADD COLUMN takes a long time

I was just trying to add a column called "location" to a table (main_table) in a database. The command I run was ALTER TABLE main_table ADD COLUMN location varchar (256); The main_table contains > 2,000,000 rows. It keeps running …

Read More
MySQL INSERT INTO table VALUES.. vs INSERT INTO table SET

What is main difference between INSERT INTO table VALUES .. and INSERT INTO table SET? Example: INSERT INTO table (a, b, c) VALUES (1,2,3) INSERT INTO table SET a=1, b=2, c=3 And what about performance of these two?

Read More
Best way to test if a row exists in a MySQL table

I'm trying to find out if a row exists in a table. Using MySQL, is it better to do a query like this: SELECT COUNT(*) AS total FROM table1 WHERE ... and check to see if the total is non-zero or …

Read More