adding a column to a large table

MySQL & MariaDB Other MySQL® Questions
1

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.

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.