homepage Welcome to WebmasterWorld Guest from
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

5+ Year Member

Msg#: 4049887 posted 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?



WebmasterWorld Senior Member rocknbil us a WebmasterWorld Top Contributor of All Time 10+ Year Member

Msg#: 4049887 posted 5:39 pm on Dec 27, 2009 (gmt 0)

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

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