GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

Altering default column value SLOW!

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Altering default column value SLOW!

    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?

  • #2
    You can try this if you want:

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

    Comment


    • #3
      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.

      Comment


      • #4
        I think that might have changed since mysql 5.0. I'm not aware of any open bug reports on that, and I think it's a good thing to report as a bug if not. If you find a bug report for that, please post the link here!

        Comment


        • #5
          I did not find a bug report for this, so I filed one:

          Bug #50771 Altering column default value causes table rebuild

          Michael Thompson

          Comment


          • #6
            An interesting regression. Ironic because now the InnoDB plugin lets you add indexes online!

            Comment

            Working...
            X