homepage Welcome to WebmasterWorld Guest from 54.204.249.184
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
Null vs Space
Gian04




msg:4049889
 1:41 pm on Dec 27, 2009 (gmt 0)

If a text field (varchar) is not required, which one is better to use INSERT NULL or INSERT ''?

Is it true that inserting null instead of space ('') will save you some disk space?

 

rocknbil




msg:4049940
 5:39 pm on Dec 27, 2009 (gmt 0)

Don't insert anything at all, and let the database default value populate the field.


$fields=$values=NULL;
foreach ($input as $key=>$value) {
if (isset($value)) {
$fields .= "$key,";
$values .= "'$value',";
}
}
if ($fields and $values) {
$fields = preg_replace('/,$/','',$fields); // or other method
$values = preg_replace('/,$/','',$values); // to chop last comma
$query = "insert into table ($fields) values ($values)";
// etc., execute query
}
else { echo "nothing to insert"; }

As for the null/empty string, this is really a decision you make based on how you plan to work the database, not how much more or less space it takes. That is, if "optional fields" are expected to be null, use null, otherwise, set the default to "not null" and it will be a blank string, which is very different than null.

problems with null [dev.mysql.com]

How much actual space depends on whether you're using myISAM or INNoDB. There are quite a few resources to be found on this, one I found that says it better than I ever could:

For MyISAM tables, NULL creates an extra bit for each NULLABLE column (the null bit) for each row. If the column is not NULLABLE, the extra bit of information is never needed. However, that is padded out to 8 bit bytes so you always gain 1 + mod 8 bytes for the count of NULLABLE columns.

In InnoDB, NULLS take no space: They simply don't exist in the data set ...... When the data is actually laid out on disk, NULL and '' take up EXACTLY THE SAME SPACE in both data types. However, when the value is searched, checking for NULL is slightly faster then checking for '' as you don't have to consider the data length in your calculations: you only check the null bit.

By that description, you can see that it's not so much the space that's an advantage with null columns without value, it's the performance.

Another example, I've encountered some perl modules that won't run if you have any columns set to "not null."

I guess if you really want to know the difference in disk space the definitive test would be to create two tables, one with not null and one with null columns, populate them with a million records and compare the sizes of the actual table files.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved