Bookmark and Share
Speeding up
(Publish Date: 2009-11-10 12:54pm, Total Visits: 889, Today: 1, This Week: 1, This Month: 4)

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 3 to 5 days. Here’s the expensive way:


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


Profiling that statement with SHOW STATUS shows that it does 1,000 handler reads and 1,000 inserts. In other words, it copied the table to a new table, even though the column’s type, size, and nullability didn’t change.
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,* 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.

 

Extracted from <High performance MySQL>