Altering a MySQL Table

In a normal world without a lot of records, altering (adding, deleting columns and indices) a table is an easy job. Have a look at the syntax here.

Its essentially creation of a copy of the original table, applying the alter table to the new table and copying row by row until the end of the table is reached. The essential problem with this is that the row by row copy locks the table because we don't want inconsistencies while we copy. I.e. no writes during the copy.

If the table has lots of rows then this becomes a real problem.

It seems that those intelligent guys have a solution to this too.

One Approach : 
- Make changes on the Slave and Upgrade the Slave to Master.

Another (Facebook's Way) :
- Create a temporary table which is a copy of the main table
- Apply the changes to the temp table
- Add something which could be used to run changes on the temp table
- Copy data from the main table to the temp table
- Lock the main table
- Replay the changes on the temp table
- Rename the temp table to the original table after renaming the original table to something else

Phew

Thanks to Martin for the star in github

http://www.facebook.com/note.php?note_id=430801045932
https://github.com/soundcloud/lhm