What would be the fastest way to add a new column in a large mysql table ?
Alter table add column creates a copy of the full table, and then replaces the old one with the new create table. While this process is running, the original table is readable, but all inserts and updates are stalled.
On large tables, the copy can take a long time, is there any way to reduce it ?
Don't do this live on an active system. For an active system, do this while you take the system down for regular maintenance.
depending on the table and how big it is (rows and or columns) there may be an alternative but for most situations the behaviour you have observed will be fastest. this is a great example of why it's important to design your schema very carefully and test with large amounts of data.
Hi Maraino,
You are stuck doing the ALTER TABLE. The best possible way to effectively deal with this, is to use a MASTER-MASTER setup.
You can modify MASTER1 first, and just use MASTER2 in production. Then you switch over and do the exact opposite.