Tag Archives: mysq

A good way to add a column to a table with millions of records, without locking resources.

Whenever you manage a database and reach a certain amount of records, some things just aren’t the same anymore.

Try adding a column, an index or even worse a FULL TEXT index. This can lock up your resources and hurt your i/o drastically, you don’t want this to happen on a production environment.
But sometimes you just have to do what you have to do.
Note:the effect often depends on your database engine e.g. innoDB

Here is a cool way to add a column without locking your resources:

CREATE TABLE big_table_copy LIKE big_table;
ALTER TABLE big_table_copy ADD COLUMN some_new_column varchar(256);
INSERT INTO big_table_copy (fields_in_big_table) SELECT * FROM big_table;
RENAME TABLE big_table TO big_table_old, big_table_copy TO big_table ; 
# DROP TABLE big_table_old;

Note: You might not want to delete the big_table_old to check if any new records were added while this query was running. And make sure they also are added in the new big_table

Tagged , , , ,