When to use NULL in MySQL tables

Question

I appreciate the semantic meaning of a NULL value in a database table, different from both false and the empty string ''. However, I have often read about performance problems when fields are nullable and been advised to use an empty string in cases where NULL is actually semantically correct.

What circumstances are appropriate to use nullable fields and NULL values? What are the trade-offs? Is it sensible to simply avoid using NULLs altogether and simply use empty strings, false or 0 to indicate the absence of a value?

UPDATE

OK - I understand the semantic difference between '' and NULL as well as the (performance-agnostic) circumstances in which NULL is the appropriate field value. However, let me expand on the hinted performance issue. This is from the excellent "High Performance MySQL" by Schwartz, Zeitsev et al http://www.borders.co.uk/book/high-performance-mysql-optimization-backups-replication-and-more/857673/:

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.

More here: Google books preview

This is quite possibly the definitive answer - I was just looking for second opinions and experience from the front-line.

Answer 1

However, I have often read about performance problems when fields are nullable and been advised to use an empty string in cases where NULL is actually semantically correct.

I'm going to be nit-picky about word choice for a moment:

• Even if it were a significant performance factor, that doesn't make it semantically correct to use a value instead of NULL. In SQL, NULL has a semantic role, to denote a missing or inapplicable value. The performance characteristics of NULL in a given RDBMS implementation are independent of this. The performance may vary from brand to brand or from version to version, but the purpose of NULL in the language is consistent.

In any case, I have not heard of any evidence that NULL performs poorly. I'd be interested in any references to performance measurements that show nullable columns perform worse than non-nullable columns.

I'm not saying I'm not wrong or that it can't be true in some cases -- just that it's not meaningful to make idle suppositions. Science is not made up of conjecture; one has to show evidence with repeatable measurements.

Metrics also tell you by how much the performance differs, so you can make a judgment about whether it's something to worth worrying about. That is, the impact could be measurable and nonzero, but still insignificant compared to greater performance factors, such as properly indexing tables or sizing your database cache.

In MySQL, searches for NULL can benefit from an index:

mysql> CREATE TABLE foo (   i INT NOT NULL,   j INT DEFAULT NULL,   PRIMARY KEY (i),   UNIQUE KEY j_index (j) );  mysql> INSERT INTO foo (i, j) VALUES    (1, 1), (2, 2), (3, NULL), (4, NULL), (5, 5);  mysql> EXPLAIN SELECT * FROM foo WHERE i = 3; +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+ | id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra | +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+ |  1 | SIMPLE      | foo   | const | PRIMARY       | PRIMARY | 4       | const |    1 |       |  +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+  mysql> EXPLAIN SELECT * FROM foo WHERE j IS NULL; +----+-------------+-------+------+---------------+---------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key     | key_len | ref   | rows | Extra       | +----+-------------+-------+------+---------------+---------+---------+-------+------+-------------+ |  1 | SIMPLE      | foo   | ref  | j_index       | j_index | 5       | const |    2 | Using where |  +----+-------------+-------+------+---------------+---------+---------+-------+------+-------------+ 

Note that's still not a measurement of performance. I've only shown that you can use an index while searching for NULL. I'm going to assert (admittedly without having measured, but hey this is just StackOverflow) that the benefit of an index overshadows any possible penalty when searching for NULL versus a blank string.

It's not a correct design decision to choose zero or blank or any other value to substitute for NULL. You may need to use those values as significant in the column. That's why NULL exists, as a value that is by definition outside the domain of values of any data type, so you can use the full range of values of integers or strings or whatever and still have something to signify "none of the above values."

Answer 2

The MySQL manual actually has a nice article about the problems with NULL.

Hope it helps.

Also found this other SO post about NULL and Performance