Recently I encountered one issue where I had to add index(alter table) to a column in table having 1.5 millions rows. As expected query would have taken several hours to execute so here’s the shortcut.
Alter table query would be taking several hours because it had to rewrite each of million rows with new key so quicker solution is to create an empty table apply index or alter operation then copy all data from old to new and replace it with newer table. It certainly saved atleast 2-4 hours of effort.
CREATE TABLE main_table_new LIKE main_table; ALTER TABLE main_table_new DO_WHATEVER_HERE; INSERT INTO main_table_new (fields_in_main_table) SELECT * FROM main_table; RENAME TABLE main_table TO main_table_old, main_table_new TO main_table; DROP TABLE main_table_old;