Altering tables
ALTER TABLE changes the structure of a table. For example, you can add or delete columns, create or destroy indexes, change the type of existing columns, or rename columns or the table itself.
While performing certain alter operations such as changing a column data type, adding a SPATIAL INDEX, dropping a primary key, converting a character set, adding/removing encryption, and so on, DML operations on the table are blocked. If the table is big, it will take even more time to alter, and the application cannot access the table during that time, which is not desired. In those situations, a pt-online-schema change is helpful, where DML statements are allowed.
There are two algorithms for alter operations:
- In-place (default): Does not require copying whole table data
- Copy: Copies the data into a temporary disk file and renames it
Only certain alter operations can be done in-place. The performance of an online DDL operation is largely determined by whether the operation is performed...
