Null in MySQL (Performance & Storage)

NULL in MySQL (Performance & Storage)

It depends on which storage engine you use.

In MyISAM format, each row header contains a bitfield with one bit for each column to encode NULL state. A column that is NULL still takes up space, so NULL's don't reduce storage. See https://dev.mysql.com/doc/internals/en/myisam-introduction.html

In InnoDB, each column has a "field start offset" in the row header, which is one or two bytes per column. The high bit in that field start offset is on if the column is NULL. In that case, the column doesn't need to be stored at all. So if you have a lot of NULL's your storage should be significantly reduced.
See https://dev.mysql.com/doc/internals/en/innodb-field-contents.html

EDIT:

The NULL bits are part of the row headers, you don't choose to add them.

The only way I can imagine NULLs improving performance is that in InnoDB, a page of data may fit more rows if the rows contain NULLs. So your InnoDB buffers may be more effective.

But I would be very surprised if this provides a significant performance advantage in practice. Worrying about the effect NULLs have on performance is in the realm of micro-optimization. You should focus your attention elsewhere, in areas that give greater bang for the buck. For example adding well-chosen indexes or increasing database cache allocation.

NULL or empty string more efficient/natural?

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.

What's the impact of NULL on MySQL tables? (InnoDB)

See:

• Does mysql index null values?
• NULL in MySQL (Performance & Storage)

NULLs are indexed.

In InnoDB, you can reduce the storage requirements for your data row by using NULL.

MySQL NULL Row Storage Size

Here is a link answering your question.
Null in Mysql

Mysql column with null values - what are the space requirements?

This depends on the ROW_FORMAT value you give when you create your table.

Before version 5.0.3, the default format is set to "REDUNDANT" : any fixed-length field will use the same space, even if it's value is NULL.

Starting with version 5.0.3, the value is set to "COMPACT" : NULL values will never use any space in your database.

You can do an ALTER TABLE to be sure to use the correct format :

ALTER TABLE ... ROW_FORMAT=COMPACT

More details here :
http://dev.mysql.com/doc/refman/5.1/en/data-size.html

---

Related Topics