NULL or empty string more efficient/natural? [closed]

Question

Pretext, I am familiar with the semantical differences between a NULL value and an empty string.

I have a MySQL table where I store a lot of hostnames with their IP addresses (as a string) and wonder what would appear to be more natural (or efficient storage-wise) in case a hostname cannot be resolved.

A NULL value or an empty string (in which case it probably should be a VARCHAR and not a CHAR)

I would tend towards a NULL value but I would like to have this confirmed or disconfirmed.

Answer 1

In MyISAM MYSQL you save one bit per row not using NULL. As it is stated here:

Declaring columns NULL can reduce the maximum number of columns permitted. For MyISAM tables, NULL columns require additional space in the row to record whether their values are NULL. Each NULL column takes one bit extra, rounded up to the nearest byte.

Take a look here as well:

In addition, while a NULL itself does not require any storage space, NDBCLUSTER reserves 4 bytes per row if the table definition contains any columns defined as NULL, up to 32 NULL columns. (If a MySQL Cluster table is defined with more than 32 NULL columns up to 64 NULL columns, then 8 bytes per row is reserved.)

Moreover it also makes the database work faster at it stated here (taken from stackoverflow - @DavidWinterbottom link didn't work for me, I added a different sourse)

It's harder for MySQL to optimize queries that refer to nullable coumns, because they make indexes, index statistics, and value comparisons more complicated. A nullable column uses more storage space and requires special processing inside MySQL. When a nullable column is indexed, it requires an extra byte per entry and can even cause a fixed-size inded (such as an index on a single integer column) to be converted to a variable-sized one in MyISAM.

In most of the cases non-NULL values behave more predictable when combined with COUNT() and other aggregating function but you can also see a NULL behave according to your needs.

As it is stated here, not all group (aggregate) functions ignore NULL for instance, COUNT() would give you different result that COUNT(*) for a column containing NULL values.

On the other hand as other point out NULL better reflects the meaning of entry - it is an unknown value and if you wanted to count all the hosts you would probably COUNT() to behave exactly as it does.