Bug #20986 renaming a column very slow on large tables
Submitted: 12 Jul 2006 9:01 Modified: 27 Sep 2008 11:01
Reporter: [ name withheld ] Email Updates:
Status: Closed Impact on me:
Category:MySQL Server Severity:S5 (Performance)
Version:5.0.21-community-nt OS:Windows (Windows XP)
Assigned to: CPU Architecture:Any

[12 Jul 2006 9:01] [ name withheld ]
try to rename a column of a table with many records (we had a table with about 4 million  records):

  ALTER TABLE tablename CHANGE old_name new_name old_type

The table was a innodb-table and the datatype of the column was int.

even though the datatype of the column was not changed, this alter table statement took about 20 Minutes to execute (on a brand new Dual-Core PC!), which is absolutely unacceptable.

How to repeat:
see description
[12 Jul 2006 10:26] Valeriy Kravchuk
Thank you for a problem report. It is a know problem: in 5.0.x each ALTER TABLE statement makes a copy of the entire table. This problem will be fixed in versions 5.1.x only.
[27 Sep 2008 11:01] Konstantin Osipov
The problem is fixed in 5.1 with fast ALTER TABLE support in the server.
Now it is entirely up to the storage engine whether ALTER TABLE RENAME COLUMN is fast or not.
If your storage engine is still slow in 5.1, I suggest to file a feature request against it.
Thank you for your interest in MySQL.