# Altering default column value SLOW!

**URL:** <https://forums.percona.com/t/altering-default-column-value-slow/1328>\
**Category:** Other MySQL® Questions\
**Created:** [January 16, 2010, 3:39pm UTC](https://forums.percona.com/t/altering-default-column-value-slow/1328 "2010-01-16T15:39:44Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![bluebutton](https://avatars.discourse-cdn.com/v4/letter/b/ce7236/32.png) [@bluebutton](https://forums.percona.com/u/bluebutton)\
**Post date:** [January 16, 2010, 3:39pm UTC](https://forums.percona.com/t/altering-default-column-value-slow/1328/1 "2010-01-16T15:39:44Z")

</div>

I have a large MyISAM table, and I would like to change the default value for one of the columns. Using mysql 5.1.31, I issue the following command:

ALTER TABLE medianet\_media\_files ALTER COLUMN feed\_created\_id SET DEFAULT 64;

Although this _should_ cause only a simple modification to the .frm file, mysql _insists_ on copying the entire table:

±—±-----±----------±---------------±--------±-----±------------------±-----------------------------------------------------------------------------+| 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)

Any ideas?

---

<div class="post-metadata">

**Author:** ![xaprb](https://avatars.discourse-cdn.com/v4/letter/x/49beb7/32.png) [@xaprb](https://forums.percona.com/u/xaprb)\
**Post date:** [January 16, 2010, 9:35pm UTC](https://forums.percona.com/t/altering-default-column-value-slow/1328/2 "2010-01-16T21:35:28Z")

</div>

You can try this if you want:

[http://www.mysqlperformanceblog.com/2007/10/29/hacking-to-ma](http://www.mysqlperformanceblog.com/2007/10/29/hacking-to-ma) ke-alter-table-online-for-certain-changes/

---

<div class="post-metadata">

**Author:** ![bluebutton](https://avatars.discourse-cdn.com/v4/letter/b/ce7236/32.png) [@bluebutton](https://forums.percona.com/u/bluebutton)\
**Post date:** [January 17, 2010, 1:47pm UTC](https://forums.percona.com/t/altering-default-column-value-slow/1328/3 "2010-01-17T13:47:56Z")

</div>

Thanks! Creating another table with an identical schema, setting the default value for columns in the empty table and then copying the .frm for that table over the .frm for the non-empty one and doing a flush table worked like a charm. I was already using this technique to delay building the indexes for the primary keys when initially loading these large tables (although in that case, the .MYI is copied too). All this brought down the run time from over 12 hours to one hour.

It should be noted that in that reference as well as the MySQL High Performance book (page 146), it is mentioned specifically that altering the default value for a column the way I was doing it should NOT cause a table rebuild. Someone even mentioned that they had verified that it does not. I suppose my experience should be considered evidence that at least in mysql 5.1.32, this is _not_ true.