DON’T ALTER TABLE. DO COPY AND RENAME

In some cases a MySQL MyISAM table structure needs to be alter. This includes adding, removing and changing table columns (or indexes) and even re-ordering the MySQL table.
In these cases, for performance and safety reasons, it is wise to avoid altering the current working MySQL table and adopt the Copy and Rename approach.

The Copy and Rename approach consist from the following steps:

• Create similar temporary table but with the requested change
• Disable the temporary table keys,
• Copy the rows from the original table to the temporary table
• Enable the temporary table keys,
• Backup the original table and rename the temporary table to have the original table name

For example, in a case where an existing MySQL table needed new column,
instead of:

Alter tbl add column colx int not null;

do the following:

Create table tbl_tmp like tbl; Alter table tbl_tmp add column colx int not null; Alter table tbl_tmp disable keys; Insert into tble_tmp select *,0 from tbl; Alter table tbl_tmp enable keys; Rename table tbl to tbl_old; Rename table tbl_tmp to tbl;

ALTER TABLE tbl_tmp DISABLE KEYS tells MySQL (for a MyISAM table) to stop updating non-unique indexes. ALTER TABLE tbl_tmp ENABLE KEYS then re-create the missing indexes. MySQL re-create indexes with a special algorithm that is much faster than inserting keys one by one, so disabling keys before performing bulk insert operations should give a considerable speedup.