MySQL’s ALTER TABLE performance can become a problem with very large tables. MySQL performs most alterations by making an empty table with the desired new structure, inserting all the data from the old table into the new one, and deleting the old table. This can take a very long time, especially if you’re short on memory and the table is large and has lots of indexes. Many people have experience with ALTER TABLE operations that have taken hours or days to complete.

MySQL 5.1 and newer include support for some types of “online” operations that won’t lock the table for the whole operation. Recent versions of InnoDB^[48] also support building indexes by sorting, which makes building indexes much faster and results in a compact index layout.

In general, most ALTER TABLE operations will cause interruption of service in MySQL. We’ll show some techniques to work around this in a bit, but those are for special cases. For the general case, you need to use either operational tricks such as swapping servers around and performing the ALTER on servers that are not in production service, or a “shadow copy” approach. The technique for a shadow copy is to build a new table with the desired structure beside the existing one, and then perform a rename and drop to swap the two. Tools can help with this: for example, the “online schema change” tools from Facebook’s database operations team (https://launchpad.net/mysqlatfacebook), Shlomi Noach’s openark toolkit (http://code.openark.org/), and Percona Toolkit (http://www.percona.com/software/). If you are using Flexviews (discussed in Materialized Views), you can perform nonblocking schema changes with its CDC utility too.

Not all ALTER TABLE operations cause table rebuilds. For example, you can change or drop a column’s default value in two ways (one fast, and one slow). Say you want to change a film’s default rental duration from three to five days. Here’s the expensive way:

mysql> ALTER TABLE sakila.film
    -> MODIFY COLUMN rental_duration TINYINT(3) NOT NULL DEFAULT 5;

SHOW STATUS shows that this statement does 1,000 handler reads and 1,000 inserts. In other words, it copies the table to a new table, even though the column’s type, size, and nullability haven’t changed.

In theory, MySQL could have skipped building a new table. The default value for the column is actually stored in the table’s .frm file, so you should be able to change it without touching the table itself. MySQL doesn’t yet use this optimization, however; any MODIFY COLUMN will cause a table rebuild.

You can change a column’s default with ALTER COLUMN,^[49] though:

mysql> ALTER TABLE sakila.film
    -> ALTER COLUMN rental_duration SET DEFAULT 5;

This statement modifies the .frm file and leaves the table alone. As a result, it is very fast.