如何从MySQL大表中删除列

Question

I have a 250GB 500M rows MySQL table and would like to delete one of its columns. The column is VARCHAR(255). I would like to perform this operation with as little as possible down-time. Thoughts:

1. Is there any configuration I can add that will help this operation?
2. Would it be better to iterate in php before on each row and delete the value inside it?
3. Would it require OPTIMIZE TABLE afterwards?
4. What would be a reasonable time estimation for such a procedure?

Answer 1

To assure minimal downtime I would personally backup and restore to an alternative database and then do the 'drop column' before restoring again.

1. link

2. It might be, but it will take longer time and is more prone to errors I would say.

3. Yes, good idea, also, consider using ANALYZE TABLE before hand

OPTIMIZE TABLE; The OPTIMIZE command should be run when the DBMS is offline for scheduled maintenance. The command is nonstandard SQL.