What is the proper way of Adding a Field in a MySQL table with 1.6 million row

Question

I have a table with 1.6 million rows.

Then I add a field on that table.

I am using hostgator as hosting. You know, they have like 16 core CPU.

Result: 1. phpmyadmin doesn't work for 10 minutes 2. After database back online the field I want to add is NOT added. 3. Fortunately I do not get a call from hostgator complaining.

I don't care it's slow. I know it's slow. It's 1.6 million row. I just want to do this once.

Is this because I use innodb engine?

The command is simple:

Alter table tablebusiness add column `Prominent` int(10) NOT NULL

I got this error:

#2013 - Lost connection to MySQL server during query

I do not need this operation to be run fast

I need it to run once.

I suppose I can copy the table somewhere else and then switch the old table into new table but innodb has cascade and I do not know how to do so.

Other update: Hostgator is pissed. Can't blame them. I must have used huge CPU power.

Looks to me there is a proper way to modify a table. Maybe shutting down the database first in maintenance mode or something. Maybe creating a copy of table first.

Answer 1

ALTER on such a huge table can be slow irrespective of Engine_type

Follow this method to avoid downtime and quicker operation:

1. Setup slave

2. Stop replication.

3. Make ALTER on slave

4. Let slave catch up the master

5. swap master and slave, so slave becomes production server with changed structure and minimum downtime

You can also refer this post from MySQL Performance blog:

http://www.mysqlperformanceblog.com/2008/03/27/using-mmm-to-alter-huge-tables/