Null

As we already stated in earlier articles in this blog
[1 and 2
] it is a good idea to use NULL values properly in MariaDB and MySQL.

One of my Mantras in MariaDB performance tuning is: Smaller tables lead to faster queries! One consequence out of this is to store NULL values instead of some dummy values into the columns if the value is not known (NULL: undefined/unknown).

To show how this helps related to space used by a table we created a little example:

CREATE TABLE big_null1 (
  id INT UNSIGNED NOT NULL …

In the recent MariaDB DBA advanced training class the question came up if MariaDB can make use of an index when searching for NULL values… And to be honest I was not sure any more. So instead of reading boring documentation I did some little tests:

Search for NULL

First I started with a little test data set. Some of you might already know it:

CREATE TABLE null_test (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
, data VARCHAR(32) DEFAULT NULL
, ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP() 
); …

Abstract:

It is common practice in MySQL table design that fields are declared as NOT NULL but some non-sense DEFAULT values are specified for unknown field contents. In this article we show why this behavior is non optimal an why you should better declare a field to allow NULL values and use NULL values instead of some dummy values.

What we can see often out in the field

Recently we had a discussion with a customer if it makes more sense to store a default value or NULL in InnoDB tables when we do not know …