Vaibhav Asthana

Technology Explorer

MySQL Performance Tuning: Alter table querie too slow on large table

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;

Hello world!

“Hello World!”, First set of words that most of the programmers usually print out on screen or console as part of their first coding lessons. This post is a salute from this blog to the world on its rebirth after being latent since 2009-10-26.

© 2009-2016 | All Rights Reserved | Vaibhav Asthana & Design by Anders Norén