GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

adding a column to a large table

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

  • adding a column to a large table

    I'm trying to add a column to a fairly large innodb table (14.6m rows) and the ALTER TABLE query has been running for almost 24 hours. This is under MySQL 5.0.22

    Is there a better/faster way to add the column that would only take an hour or two? It's been running for so long I'm wary of trying a new method unless I know it will finish relatively quickly.

    Along these lines, is there a way to measure the progress of the ALTER TABLE? I've been monitoring the size of the ibdata1 file, but that's not a very good indicator, as other things are happening the database while the alter is running.

    Thanks!

  • #2
    There are some tricks with making alter work faster. (see http://www.mysqlperformanceblog.com/2007/10/29/hacking-to-ma ke-alter-table-online-for-certain-changes/#comment-182689 for details), But unfortunately it does not help in case when you add columns.

    Comment


    • #3
      It finally finished last night after about 34 hours.

      I was hoping there was some way I could export the table, import into a new table, and copy over the old one, but I guess that is what MySQL is doing internally anyways, huh.

      Comment

      Working...
      X