MySQL Forums
Forum List  »  Newbie

Re: Drop column of big table
Posted by: Zach Ellis
Date: October 21, 2023 07:35PM

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.

Zach Ellis

Options: ReplyQuote

Written By
Re: Drop column of big table
October 21, 2023 07:35PM
October 21, 2023 09:01PM

Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.