Bug #1058 Add ability to alter tables without rebuilding the table
Submitted: 15 Aug 2003 21:04 Modified: 21 Aug 2003 4:40
Reporter: David Jeske Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S4 (Feature request)
Version: OS:
Assigned to: CPU Architecture:Any

[15 Aug 2003 21:04] David Jeske
Description:
I'd like to see the ablity to alter tables without rebuilding the table and
without taking it offline. In particular, column additions, and index
additions. We have multi-gigabyte tables which we just consider 'unalterable' because of this problem because we can't take the system offline for this length of time.

For example, other databases (Oracle, MSSQL, Postresql) handle row additions by tracking 'row format versions' and defining a new row format without changing the data in any of the existing rows. New rows are written using the new format automatically.

How to repeat:
To see the problem, build a really big table, then issue an "alter table foo add column bar.." command, and watch the system churn away. Then go repeat this on a different database and notice that it's instant.
[21 Aug 2003 4:40] Peter Zaitsev
David,

Thank you for your feedback. Yes we have such "online alter table" in plans,
at least for some table types. 

Shorter term we're planning to fix alter table not to rebuild whole table if indexes are added/dropped, which would allow to increase alter table performance dramatically.