Recently I need to modify a column in a table which contains over millions of rows. To run the update requires a few minutes of processing time and it might be very critical for the websites which get millions or billions of hits a day. So before you run the ALTER TABLE command, a few facts you need to be aware of:
[generic]
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. The temporary table is created in the database directory of the new table. This can be different from the database directory of the original table if ALTER TABLE is renaming the table to a different database.
[/generic]
In the following cases, no temporary table is necessary:
1. Some changes are fast by only modifying the table metadata but not the table data, like renaming a column or index, changing the default value of a column or update a column type from varchar(20) to varchar(50).
2. Rename table without other options.
which means the operation would be faster in those cases.
