MySQL: default none or null?

[csdude55]

It's my understanding that NULL takes up more space than '', so as I'm rebuilding my table structure I wasn't planning to use NULL. Instead, I just set the columns to default to none.

But now I'm doing an insert in Perl, like:

$foo = param('foo');

my $sth = $dbh->do("INSERT INTO table (colA)
 VALUES (?)", undef,
  $foo) or die "Couldn't execute INSERT: " . $dbh->errstr;

(I'm showing it in Perl, just in case the problem I'm having is with Perl instead of MySQL)

But when param('foo') is empty, I'm getting an error of:

Couldn't execute INSERT: Column 'colA' cannot be null at example.cgi line 123.

What's the "best" way to fix this? Change the structure to "As define" and leave the value as empty? My goal here is to optimize database storage and performance, and not regret my choices in 6 years and start restructuring all of the tables like I'm doing now! lol

[tangor]

How much overhead (space) are you actually saving? Over how many records? Are you seeing significant timing/benchmarks in improvement?

[csdude55]

I honestly don't know yet, I'm still early in the production. But I'm rebuilding everything, anyway, so I figure... why not?

After I get the new build finished I'm planning a large statewide marketing campaign, too, so I'm loosely preparing for a potential boom in user submitted data. From my experience, every fraction of a second that I can save on a page load results in an increase of pages per session, so saving 100ms on a few queries could have a significant long term financial impact.

As for coding, after playing around today it looks like I can leave it as NOT NULL and default to None, and then:

$foo = param('foo');

if (!$foo) { $foo = ''; }

my $sth = $dbh->do("INSERT INTO table (colA)
 VALUES (?)", undef,
 $foo) or die "Couldn't execute INSERT: " . $dbh->errstr;

I guess there's a programmatic difference between setting $foo to a value that doesn't exist versus setting it to ''?

[tangor]

Personally never dealt with numbers large enough at scale to give an answer ... most of my "large" stuff has been in the low millions. Pretty small fry stuff.

[graeme_p]

I agree with @tangor its premature optimisation to think about storage and performance now.

However, there is a more important reason why you might prefer '' over null for a char or text column is that is that if you allow null you have two possible "blank" values (e.g. corresponding to blank in a form). NOT NULL means it has to be blank.

However, the way they behave is different. For example, you can have multiple null values in a column with a unique index, and null behaves entirely differently from '' (or anything else) when sorting or comparing values. Which of those behaviours makes sense is the most important consideration. If they do not matter than think about the two possible blank values issue.

[topr8]

i had a feeling that the answer to this question is not to do with space - but i wasn't sure why it might be better to have blank/empty over NULL.

so i checked in my reference library!

Avoid Null if possible.
...
It's harder for MySQL to optimize queries that refer to nullable columns, because they make indexes, index statistics, and value comparisons more complicated. A nullable column uses more storage space and requires special processing inside MySQL.

credit: High Performance MySQL by O'Reilly by Schwartz, Zaitsev, Tkachenko, Zawodny, Lentz and Balling

i thoroughly recommend buying this book if you are building a potentially large db from scratch, it goes into a lot of detail about stuff in a way i just haven't seen online. i've got the 2nd edition and doubtless there is a newer version now ...

also be aware that depending on your linux distro you may well be using MariaDB not MySQL and not even know it, and there are subtle differences ... MariaDB do various training sessions occasionally and you can get to talk to some of the guys who are actually developing it. i've been to a day session with them and at the very least you get excellent sandwiches and coffee (for free)!

[tangor]

sandwiches and coffee (for free)!

My kind of tech! Even better with brews. :)

[topr8]

@tangor haha, agreed!

[graeme_p]

Some useful info here: [dev.mysql.com...]

and here:

[stackoverflow.com...]

and possibly this: [dev.mysql.com...]