I want to add a new column to my production database. I would like to add a default value in order to prevent all the if not null and ... business in all my queries (both new and old).
What order of add column-time penalty will I pay? I would ASSUME and HOPE that MySql would be smart enough to not actually fill in the default values for all the old rows, and instead treat the NULL value as the default (in other words, internalize the "if not null…" into the query automatically). … and thus there should be NO penalty over and above the normal column add.
To be explicit, here are the two different MySQL alter table commands: in 5.1, is there any difference in the time efficiency of the two commands?
alter table XYZ add column newcol int ;
and
alter table XYZ add column newcol int default 0;
I assume that the answer would be version-specific. Am doing this in AWS EC2-land. Here's the version string:
mysql Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1
Best Answer
You're using mysql 5.1, This means the only engines you'll have available are MyISAM or innodb. in both cases adding a new column, regardless of nullability will require a complete, blocking, table rebuild during alter table.
You maybe be able to use the pt-online-schema change tool that does some tricks to rebuild the table in a less blocking manner but may not work out if you have complicated composite primary keys.
If you have a more recent version (5.6.22 Percona Build) you could use a tokudb storage engine that allows you to to "instantly" add columns. The way it's indexing works it propagates changes down as they're accessed.