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 ;


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

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.

  • Very good, thanks! To make sure I understand you correctly, for the version of MySql I am using, the answer is that there is NO penalty for creating a new column with default value, correct? – Mark Gerolimatos Nov 17 '15 at 23:26
  • I was trying to say the exact opposite. Note the Ver 14.14 isn't what you're really wanting to consider here, it's the distribution 5.1.73 – atxdba Nov 17 '15 at 23:28
  • I think we might be confusing each other: to be explicit: Is the processing time for the two lines equivalent (or at least within the same big-O time): alter table XYZ add column newcol int ; alter table XYZ add column newcol int default 0; – Mark Gerolimatos Nov 17 '15 at 23:30
  • Either would be O(n) with respect to number of rows. Add a coefficient if you have lots of large texts/blobs that need to be copied in the process. Mysql is rarely "that smart", especially with 5.1 – atxdba Nov 17 '15 at 23:57
  • Even though it says to avoid saying "Thanks!", I am saying it...Thanks! – Mark Gerolimatos Nov 18 '15 at 0:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.