1

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
Improve this question

1 Answer 1

Reset to default
2

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.

Improve this answer
5
  • 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? Commented Nov 17, 2015 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 Commented Nov 17, 2015 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; Commented Nov 17, 2015 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 Commented Nov 17, 2015 at 23:57
  • Even though it says to avoid saying "Thanks!", I am saying it...Thanks! Commented Nov 18, 2015 at 0:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.