Table alteration is never a cheap task to do, specially when dealing with large tables containing hundreds of GB data. ALTER TABLE may be required to add/remove column or indexes. Easiest solution to run it on production is to take the server one by one out of rotation, alter table, put it back in and sync up the data in between with other servers.
However, If it can't be done the only solution adopted by normal users is to fire the ALTER TABLE on production ending in ages to finish it, resulting in website down.
If some key is need to be dropped with the condition of no writes, we have one alternate solution for the same, to make ALTER TABLE faster.
Say there is some huge test table containing lot of data:
CREATE TABLE `test` (
`rollno` INT UNSIGNED NOT NULL DEFAULT '0',
`name` varchar(100) DEFAULT NULL,
`score` MEDIUMINT UNSIGNED DEFAULT '0',
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`)
) ENGINE=MyISAM;
Now If there is a requirement to drop unique key 'name', so create a new table say test_new:
CREATE TABLE `test_new` (
`rollno` INT UNSIGNED NOT NULL DEFAULT '0',
`name` varchar(100) DEFAULT NULL,
`score` MEDIUMINT UNSIGNED DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM;
Flush both tables with read lock:
mysql> FLUSH TABLES WITH READ LOCK;
Open another terminal. And go to the mysql/data/ directory. Do the following:
mysql/data/test $ mv test.frm test_old.frm; mv test_new.frm test.frm; mv test.MYI test_old.MYI; mv test_new.MYI test.MYI;
Table definitions are switched here. The table definition of test now doesn't contain unique key.
Now unlock the tables in the main window. And run repair tables to remove any issues.
mysql> UNLOCK TABLES;
mysql> REPAIR TABLES test;
+---------------+--------+----------+-------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+---------------+--------+----------+-------------------------------------------+
| test.testhack | repair | warning | Number of rows changed from 0 to 20000000 |
| test.testhack | repair | status | OK |
+---------------+--------+----------+-------------------------------------------+
The repair table rebuilds the indexes. It is faster since it skips the use of key_cache for rebuilding the index (which is used in a normal alter table scenario).
Note, don't forget to take the backup in the advance in case if anything goes wrong.
