Re: Drop column of big table

[Adrián García Castro]

Hi Zach,

Ty so much for replying :)

The database is InnoDB, yes. I read about ALGORITHM=INSTANT and tried to use it but I got this message. I read about a bug with ALGORITHM=INSTANT but in my version (8.0.27) its fixed apparently.

ERROR 1845: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE.
SQL Statement:
ALTER TABLE `schema`.`seeds`
DROP COLUMN `average`, ALGORITHM = INSTANT


The script I ended running was the next one:

ALTER TABLE `schema`.`seeds`
DROP COLUMN `average`, ALGORITHM = INPLACE, LOCK = NONE;


Was running for 2 hours but it crash...

I only need 3 columns in the table, I cant optimized the data types more than this.
ID- UNSIGNED INT (PK).
SEED - UNSIGNED INT.
ENCOUNTER - SMALLINT autogenerated.


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

This options sounds good to be honest, if I remove the PK the column drop might be faster?


About this one "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."

I think it would be faster to LOAD DATA LOCAL INFILE from CSV files and get the table without the column, I estimate that this process can last 10-11 hours but might be faster that a new table.


Again ty so much for helping me man!!