Re: Drop column of big table

[Zach Ellis]

Hello Adrian,

New to mysql if someone more experienced comes go with them.

15.12 InnoDB and Online DDL would give it a read as it would be useful to your situation if your mysql version supports it.

15.12.1 Online DDL Operations the below text is from the column operations section about drop column.

If an algorithm other than ALGORITHM=INSTANT is used, data is reorganized substantially, making it
an expensive operation.

I would read the 15.12 InnoDB and Online DDL chapter to get a better understanding of the problem at hand as it goes in depth about the nuance for altering tables and such.

You could also build a new table and load the data into the new table without the column could be faster by doing it in small batches.

removing indexes on the table then dropping the column then re adding the indexes back could also be faster.

based on the table structure the data types for the columns could be changed to make the table more optimized. Using data types that use less bytes but will meet the needs of the table. This will take time since it has to rebuild all the rows of the table but the table will be more optimized after.

from my limited reading on this working and altering large tables like this will cost a lot so preparation must be done before taking any action.

regards
Zach Ellis