Why does it take so long to rename a column in mysql?

Question

I have a 12 GB table full of pictures, I'm trying to rename the blob column that holds the data, and it is taking forever. Can someone give me a blow by blow account of why it is taking so long to rename the column? I would have thought that this operation would be pretty quick, no matter the size of the table?

EDIT: The query I ran is as follows

 alter table `rails_production`.`pictures` change `data` `image_file_data` mediumblob NULL

It appears that most of the time is spent waiting for mysql to make a temporary copy of the pictures table, which since it is very large is taking a while to do.

It is on the list of things to do, to change the picture storage from the database to the filesystem.

EDIT2: Mysql Server version: 5.0.51a-24+lenny2 (Debian)

Answer 1

Because MySQL will rebuild the entire table when you make schema changes.

This is done because it's the only way of doing it in some cases, and it makes it much easier for the server to rebuild it anyway.

Answer 2

Yes mysql does a temporary copy of the table. I don't think there's an easy way around that. You should really think about to store the pictures on the filesystem and only store paths in mysql. That's the only way to fasten it up, I guess.

Answer 3

I can't give you the blow-by-blow (feature request #34354 would help, except that it probably wouldn't be back-ported to MySQL 5.0), but the extra time is due to the fact that an ALTER ... CHANGE may change the type of the column (and column attributes, if any), which necessitates converting the values stored in the column and other checks. MySQL 5.0 doesn't include optimizations for when the new type and attributes are the same as the old. From the documentation for ALTER under MySQL 5.0:

In most cases, ALTER TABLE works by making a temporary copy of the original table. The alteration is performed on the copy, and then the original table is deleted and the new one is renamed. While ALTER TABLE is executing, the original table is readable by other sessions. Updates and writes to the table are stalled until the new table is ready, and then are automatically redirected to the new table without any failed updates.

[...]

If you use any option to ALTER TABLE other than RENAME, MySQL always creates a temporary table, even if the data wouldn't strictly need to be copied (such as when you change the name of a column).

Under 5.1, ALTER has some additional optimizations:

In some cases, no temporary table is necessary:

• Alterations that modify only table metadata and not table data can be made immediately by altering the table's .frm file and not touching table contents. The following changes are fast alterations that can be made this way:

  • Renaming a column or index.

[...]