NULL in MySQL (Performance & Storage)

Question

What exactly does null do performance and storage (space) wise in MySQL?

For example:

TINYINT: 1 Byte TINYINT w/NULL 1 byte + somehow stores NULL?

Answer 1

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 http://forge.mysql.com/wiki/MySQL_Internals_MyISAM#Introduction

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 http://forge.mysql.com/wiki/MySQL_Internals_InnoDB#FIELD_CONTENTS

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.

Answer 2

Will adding a NULL bit speed up searching? (In comparison to just leaving the field empty)