[PATCH] Optimization for dropping multiple columns in MySQL

Dropping multiple columns on a large table in MySQL (at least version 5.0) can be slow proportional to the number of columns being dropped. That can be a drag on a large production database:

...MySQL, for almost all ALTER TABLE commands, copies the entire table into a temporary table, then renames the new table and drops the old one. ... The only type of ALTER TABLE call that doesn’t copy the entire table is a table rename.

(from http://labs.animoto.com/2008/08/06/activerecord-column-transformati...)

MySQL offers an extension to SQL that allows multiple columns to be dropped at once, so at least you only have to go through that once:

You can issue multiple ADD, ALTER, DROP, and CHANGE clauses in a single ALTER TABLE statement, separated by commas. This is a MySQL extension to standard SQL, which allows only one of each clause per ALTER TABLE statement. For example, to drop multiple columns in a single statement, do this:

ALTER TABLE t2 DROP COLUMN c, DROP COLUMN d;

(from http://dev.mysql.com/doc/refman/5.0/en/alter-table.html)

Here's a patch that overrides MysqlAdapter#remove_column to issue multiple drops in a single statement.

I'm assuming this doesn't need additional tests since it's an implementation detail optimization of a feature that's already covered by tests but I'm happy to write some if someone has an idea about what to write.

The patch was applied to Rails 3.0 but the same diff should apply to Rails 2.3 as well.