Altering the data type of a column in a HUGE table. Performance issues

Question

I want to run this on my table:

ALTER TABLE table_name MODIFY col_name VARCHAR(255)

But my table is huge, it has more than 65M (65 million) rows. Now when I execute, it takes nearly 50mins to execute this command. Any better way to alter table?

Answer 1

Well, you need

 ALTER TABLE table_name CHANGE col_name new_name VARCHAR(255)

But, you are right, it takes a while to make the change. There really isn't any faster way to change the table in MySQL.

Is your concern downtime during the change? If so, here's a possible approach: Copy the table to a new one, then change the column name on the copy, then rename the copy.

You probably have figured out that routinely changing column names in tables in a production system is not a good idea.

Answer 2

another variant to use percona toolkit https://www.percona.com/doc/percona-toolkit/2.2/pt-online-schema-change.html