Add an index to a large table

Add an index to a large table

Adding an index to a large table can cause problems if the table is heavily used. It may take a very long time and may even fail. The solution is quite simple. Create a new table based on the old table and add the index to the new table. Then copy all the data like this:

CREATE TABLE `NewTable`  LIKE `CurrentTable`;
ALTER TABLE `NewTable` ADD INDEX `SomeColumnIndex` (`SomeColumn`)
insert into `NewTable` select * from `CurrentTable`;

If the table is very large this can take some time. When the query is done run the following queries to make sure newly added records that are created during the copy are inserted into the new table. Then change the table names:

START TRANSACTION;
insert into `NewTable` select * from `CurrentTable` where id > (Select max(id) from `NewTable`) ;

RENAME TABLE `CurrentTable` TO `OldTable`;

RENAME TABLE `NewTable` TO `CurrentTable`;
COMMIT;

If records are changed during the copy the new table does not contain those changes. This can be fixed by placing a marker (like a timestamp) on the records that were changed and then updated the records in the new table.