[ACCEPTED]-Why does it take so long to rename a column in mysql?-mysql
I can't give you the blow-by-blow (feature 32 request #34354 would help, except that it probably 31 wouldn't be back-ported to MySQL 5.0), but 30 the extra time is due to the fact that an 29 ALTER ... CHANGE may change the type of the column (and 28 column attributes, if any), which necessitates 27 converting the values stored in the column 26 and other checks. MySQL 5.0 doesn't include 25 optimizations for when the new type and 24 attributes are the same as the old. From 23 the documentation for ALTER under MySQL 5.0:
In 22 most cases, ALTER TABLE works by making 21 a temporary copy of the original table. The 20 alteration is performed on the copy, and 19 then the original table is deleted and the 18 new one is renamed. While ALTER TABLE is 17 executing, the original table is readable 16 by other sessions. Updates and writes to 15 the table are stalled until the new table 14 is ready, and then are automatically redirected 13 to the new table without any failed updates.
[...]
If 12 you use any option to ALTER TABLE other 11 than RENAME, MySQL always creates a temporary 10 table, even if the data wouldn't strictly 9 need to be copied (such as when you change 8 the name of a column).
Under 5.1, ALTER has some 7 additional optimizations:
In some cases, no 6 temporary table is necessary:
Alterations 5 that modify only table metadata and not 4 table data can be made immediately by altering 3 the table's .frm file and not touching table 2 contents. The following changes are fast 1 alterations that can be made this way:
- Renaming a column, except for the InnoDB storage engine.
[...]
Because MySQL will rebuild the entire table 4 when you make schema changes.
This is done 3 because it's the only way of doing it in 2 some cases, and it makes it much easier 1 for the server to rebuild it anyway.
Yes mysql does a temporary copy of the table. I 5 don't think there's an easy way around that. You 4 should really think about to store the pictures 3 on the filesystem and only store paths in 2 mysql. That's the only way to fasten it 1 up, I guess.
More Related questions
We use cookies to improve the performance of the site. By staying on our site, you agree to the terms of use of cookies.