How can I speed-up the table reconstruction in MySQL when altering the schema?

I have a relatively big MySQL InnoDB table (compressed), and I sometimes need to alter its schema (increasing column size or adding a field).

It takes around 1 hour for a 500 MB table with millions of rows, but the server doesn't seem to be very busy (CPU @5%, not much RAM used, and 2.5 MB/s as I/O).

The table is not used in production so there are no concurrent requests at the same time. There is only a prim开发者_开发知识库ary index (on the first 5 columns) and one foreign key constraint.

Do you have any suggestion on how to speed-up the table alteration process?

Changing storage engine (to newer generation engines like TokuDB) seems the way to go, until InnoDB is "fixed".

Would be helpful to know the exact table and primary key/index definitions, and of lesser importance, the row count to the nearest million, although I would guess as the table is only 500mb it's probably less than 20 million rows. Also, your approach to changing the table - are you creating a new schema and inserting into it, or using a alter table etc.

I've had success in this area before with approaches like

• changing the index key composition, adding a unique key
• dropping the indexes first, then changing the table, then adding the indexes back. sometimes independent indexes can be a real performance killer if they are affected by the change.
• optimizing the table structure to remove unneeded or oversized columns
• separating out data (normally columns but you can vertically partition in some circumstances) that won't change from the core structure that might change, so you only churn a smaller part of your table