Welcome to WebmasterWorld Guest from 3.84.130.252

Forum Moderators: open

MySQL: default none or null?

     
3:16 am on Apr 27, 2019 (gmt 0)

Senior Member

WebmasterWorld Senior Member 5+ Year Member Top Contributors Of The Month

joined:Mar 15, 2013
posts: 1095
votes: 103


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
11:26 am on Apr 27, 2019 (gmt 0)

Senior Member from US 

WebmasterWorld Senior Member tangor is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Nov 29, 2005
posts:9721
votes: 928


How much overhead (space) are you actually saving? Over how many records? Are you seeing significant timing/benchmarks in improvement?
6:50 pm on Apr 27, 2019 (gmt 0)

Senior Member

WebmasterWorld Senior Member 5+ Year Member Top Contributors Of The Month

joined:Mar 15, 2013
posts: 1095
votes: 103


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 ''?
7:31 am on Apr 28, 2019 (gmt 0)

Senior Member from US 

WebmasterWorld Senior Member tangor is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Nov 29, 2005
posts:9721
votes: 928


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.
11:35 am on Apr 28, 2019 (gmt 0)

Senior Member from GB 

WebmasterWorld Senior Member graeme_p is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Nov 16, 2005
posts: 2938
votes: 188


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.
7:30 pm on Apr 29, 2019 (gmt 0)

Senior Member

WebmasterWorld Senior Member topr8 is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Apr 19, 2002
posts:3473
votes: 76


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)!
6:27 am on Apr 30, 2019 (gmt 0)

Senior Member from US 

WebmasterWorld Senior Member tangor is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Nov 29, 2005
posts:9721
votes: 928


sandwiches and coffee (for free)!

My kind of tech! Even better with brews. :)
9:25 am on Apr 30, 2019 (gmt 0)

Senior Member

WebmasterWorld Senior Member topr8 is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Apr 19, 2002
posts:3473
votes: 76


@tangor haha, agreed!
1:31 pm on Apr 30, 2019 (gmt 0)

Senior Member from GB 

WebmasterWorld Senior Member graeme_p is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Nov 16, 2005
posts: 2938
votes: 188


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

and here:

[stackoverflow.com...]

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

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members