Description:
Since setting the default value of a column should only affect *future* rows,
it should not require a table rebuild (copy to tmp, repair by sorting).
However, at least in 5.1.31, this is exactly what happens:
+----+------+-----------+----------------+---------+------+-------------------+------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+----------------+---------+------+-------------------+------------------------------------------------------------------------------+
| 90 | root | localhost | mog_production | Query | 48 | copy to tmp table | ALTER TABLE medianet_media_files ALTER COLUMN feed_created_id SET DEFAULT 64 |
| 91 | root | localhost | mog_production | Query | 0 | NULL | show full processlist |
+----+------+-----------+----------------+---------+------+-------------------+------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
Here is what my table looks like:
mysql> describe medianet_component_rights;
+-------------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+-------------+------+-----+---------+----------------+
| id | bigint(21) | NO | PRI | NULL | auto_increment |
| comp_id | int(11) | YES | MUL | NULL | |
| asset_code | varchar(3) | YES | | NULL | |
| authorization | varchar(32) | YES | | NULL | |
| inception | date | YES | MUL | NULL | |
| created_date | date | YES | | NULL | |
| last_updated_date | date | YES | | NULL | |
| feed_id | int(11) | YES | MUL | NULL | |
| feed_created_id | int(11) | NO | MUL | 64 | |
| revoked | tinyint(1) | NO | MUL | 0 | |
+-------------------+-------------+------+-----+---------+----------------+
10 rows in set (0.00 sec)
And this is a profile of that alter table statement:
Status Duration
starting 0.000037
checking permissions 0.000006
checking permissions 0.000022
init 0.000063
Opening tables 0.000016
setup 0.000166
creating table 0.008746
After create 0.000243
copy to tmp table 266.418131
Creating index 0.000019
Repair by sorting 999.999999
Saving state 0.011707
Creating index 0.000005
copy to tmp table 0.000031
rename result table 3.841147
System lock 0.000016
Table lock 0.000023
end 0.000014
query end 0.000008
freeing items 0.000154
cleaning up 0.000011
How to repeat:
Create a MyISAM table with a lot of rows (mine had >30M)
Alter the default value of a column using ALTER TABLE {tablename} ALTER COLUMN {columnname} SET DEFAULT {value};
This should be pretty much instantaneous, but took over 20 minutes for my table.