Most efficient way to alter a 4 billion row innodb table?
Posted on 2006-04-05
Hi All - I am using MySQL 4.1.10 on RedHat. I have one HUGE innodb table, it has 4.2 billion rows and is almost 500G in size. We are in a situation where we would like to add two columns to the table, eg, something like;
ALTER TABLE big ADD (
test_id INTEGER UNSIGNED NOT NULL,
test_id2 INTEGER UNSIGNED NOT NULL
However, in testing with a smaller table(23 million rows) this operation not only takes a long time, but the size of the table almost doubles. The size growth seems unusual given that we aren't actually adding data yet. I am wondering if this is expected, or if there is a better way to add the columns, or if migrating up to mysql V5 would help?