MySQL : Altering Huge Tables
You have a huge mysql table - maybe 100 GB. And you need to run alter on it - to either add an index, drop an index, add a column or drop a column. If you run the simple mysql "alter table" command, you will end up spending ages to bring the table back into production. Here is a simple hack to get the thing done. The benefit of the hack is that the alter runs quite fast. But since this is a hack, you will need to take care of the backups - in case anything goes wrong. I have seen this hack work effectively with both MyISAM and InnoDB tables. Here I have created a simple table to show this hack process. You can assume that this table has billions of rows and is more than 100GB in size. CREATE TABLE `testhack` ( `id` int(11) NOT NULL DEFAULT '0', `unq` varchar(100) DEFAULT NULL, `keyword` varchar(250) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `unq` (`unq`) ) ENGINE=MyISAM I need to drop the unique key. So, i have create a new table 'testhack_new' with the fol