I am working on finding the fastest way to add new column in a big table. The big table has about 30 millions rows and 30 columns, and another three big tables refers to its primary key. Our database is Percona 5.6.28. Engine is Innodb, I know this is a hard work in MySQL.
The current way I thought after some tests:
- set sql_log_bin = 0
- Create new table with new column and no second indexes.
- Insert into old table select new table. It takes about 1 hours 30 minutes.
- Add 7 indexes same as old table’s ones to new table once, It takes about 5 hours 30 minutes.
- set FOREIGN_KEY_CHECKS=0, drop old table, It takes 3 seconds.
- Rename new table name to old table name
The process cost about 7 hours.
step 4,5 is same as pt-online-schema-change tools -drop swap.
Until now, I don’t know how to improve the process. I want to short time to 4 hours. Is it possible?