How to insert a new column into a huge MYSQL database table?

Question

I have this table in a MYSQL database that contains about 10 million records / rows. I want to insert a new column into a table. However, a simple query for an insert column does not work well for me.

Here is what I tried, ALTER TABLE contacts ADD processed INT(11);

I waited about 5 hours, but nothing happened. Is there a way to insert a new column into such a huge table?

Hope I understood my question. Any help would be appreciated.

Answer 1

If this is a production:

You should use the pt-online-schema-change Percona Toolkit .

pt-online-schema-change emulates how MySQL modifies tables internally, but works on a copy of the table you want to modify. This means that the source table is not locked, and clients can continue to read and modify data in it.

pt-online-schema-change works by creating an empty copy of the table to modify, changing it as desired, and then copying the rows from the original table to the new table. When the copy is complete, it deletes the original table and replaces it with a new one. By default, it also deletes the original table.

Or oak-online-alter-table , which is part of the openark kit

oak-online-alter-table allows you to not block ALTER TABLE operations, rebuild tables and create a ghost table.

Changing tables will be slower, but it does not block tables.

If this is not production and downtime is OK, try this approach:

 CREATE TABLE contacts_tmp LIKE contacts; ALTER TABLE contacts_tmp ADD COLUMN ADD processed INT UNSIGNED NOT NULL; INSERT INTO contacts_tmp (contact_table_fields) SELECT * FROM contacts; RENAME TABLE contacts_tmp TO contacts, contacts TO contacts_old; DROP TABLE contacts_old;