Geek Answers Handbook

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

I want to run this in my table:

ALTER TABLE table_name MODIFY col_name VARCHAR(255)

But my table is huge, it has over 65 million (65 million) rows. Now when I execute, it takes about 50 minutes to execute this command. Any better way to modify a table?

+8
mysql alter
source share
3 answers

Well you need

  ALTER TABLE table_name CHANGE col_name new_name VARCHAR(255)

But, you are right, it takes some time to make changes. There really is no faster way to modify a table in MySQL.

Is your anxiety downtime during change? If so, an approach is possible here: copy the table to a new one, then change the column name to a copy, then rename the copy.

You probably realized that regularly changing column names in tables in a production system is not a good idea.

+3
source share

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

+2
source share

You can work with circuit changes without downtime with Oak.

oak-online-alter-table copies the schema of the source table, applies your changes, and then copies the data. You can still invoke CRUD operations, as the oak puts triggers on the source table, so data will not be lost during the operation.

Turn to another question , where the author of the oak gives a detailed explanation of this mechanism and also offers other tools.

0
source share

More articles:


All Articles