I'm dropping a column from a large InnoDB (actually Percona XtraDB 5.1.47) table of 136 million rows taking about 35Gb on disk.
To test the process, I copy the entire table (using create table... select), which only takes 14 minutes. I then drop the column, which only takes 6 minutes.
If I do exactly the same operation on the original table (on the same server, in the same database), it takes over 6 hours, nearly all of which is a 'copy to temp table'.
Given that the table has the same schema, data, indexes, and is the same size on disk as my copied version, I'm a bit mystified by this. What's making it so slow?
The only thing I can think of is perhaps it relates to foreign key checks from other tables, which wouldn't exist for the copy. My next step is to try disabling keys during the process, and then perhaps making the changes when creating a copy before renaming the new table into place, effectively emulating the DDL operation manually. Any other ideas?
Update: Discovered something: CREATE TABLE x LIKE y; DOES recreate indexes the same as for the original table. CREATE TABLE x SELECT * FROM y; DOES NOT, and it also loses the engine type, reverting to MyISAM unless you specify it manually. That probably explains a chunk of the difference.