MySQL performance of adding a column to a large table

Are there other ways to improve performance of adding a column to a large table?

Short answer: no. You may add ENUM and SET values instantly, and you may add secondary indexes while locking only for writes, but altering table structure always requires a table copy.

Long answer: your real problem isn't really performance, but the lock time. It doesn't matter if it's slow, it only matters that other clients can't perform queries until your ALTER TABLE is finished. There are some options in that case:

1. You may use the pt-online-schema-change, from Percona toolkit. Backup your data first! This is the easiest solution, but may not work in all cases.

2. If you don't use foreign keys and it's slow because you have a lot of indexes, it might be faster for you to create a copy of the table with the changes you need but no secondary indexes, populate it with the data, and create all indexes with a single alter table at the end.

3. If it's easy for you to create replicas, like if you're hosted at Amazon RDS, you may create a master-master replica, run the alter table there, let it get back in sync, and switch instances after finished.

UPDATE

As others mentioned, MySQL 8.0 INNODB added support for instant column adds. It's not a magical solution, it has limitations and side-effects -- it can only be the last column, the table must not have a full text index, etc -- but should help in many cases.

You can specify explicit ALGORITHM=INSTANT LOCK=NONE parameters, and if an instant schema change isn't possible, MySQL will fail with an error instead of falling back to INPLACE or COPY. Example:

ALTER TABLE mytable
ADD COLUMN mycolumn varchar(36) DEFAULT NULL,
ALGORITHM=INPLACE, LOCK=NONE;

https://mysqlserverteam.com/mysql-8-0-innodb-now-supports-instant-add-column/

I know this is a rather old question but today i encountered a similar problem. I decided to create a new table and to import the old table in the new table. Something like:

CREATE TABLE New_mytable  LIKE mytable ;

ALTER TABLE New_mytable  ADD some_column CHAR(1) NOT NULL DEFAULT 'N';

insert into New_mytable  select * from mytable ;

Then

START TRANSACTION;
insert into New_mytable  select * from mytable where id > (Select max(id) from New_mytable) ;

RENAME TABLE mytable TO Old_mytable;

RENAME TABLE New_mytable TO mytable;
COMMIT;

This does not make the update process go any faster, but it does minimize downtime.

Hope this helps.

MariaDb 10.3, MySQL 8.0 and probably other MySQL variants to follow have an "Instant ADD COLUMN" feature whereby most columns (there are a few constraints, see docs) can be added instantly with no table rebuild.

• MariaDb: https://mariadb.com/resources/blog/instant-add-column-innodb
• MySQL: https://mysqlserverteam.com/mysql-8-0-innodb-now-supports-instant-add-column/

What about Online DDL?

http://www.tocker.ca/2013/11/05/a-closer-look-at-online-ddl-in-mysql-5-6.html

Maybe you would use TokuDB instead:

http://www.tokutek.com/products/tokudb-for-mysql/