Still Using NULL? How It Can Quietly Slow Down Your Database

MySQL adds a NULL constraint to a column when the NOT NULL keyword is omitted, allowing the column to store NULL values. Many developers lazily accept this default, but it can introduce uncertainty in query results and degrade performance.

Null is a special constraint of columns. The columns in table will be added null constrain if you do not define the column with “not null” key words explicitly when creating the table. Many programmers like to define columns by default because of the conveniences (reducing the judgement code of nullibility) which consequently cause some uncertainty of query and poor performance of database.

Common belief claims that columns containing NULL invalidate indexes, yet the IS NULL operator can still use an index, exposing a flaw in that statement.

NULL handling operators

IS NULL

IS NOT NULL

<=>

(null‑safe equal) IFNULL() (function)

Examples:

create table test_null(
    id int not null,
    name varchar(10)
);
insert into test_null values (1, 'zlm');
insert into test_null values (2, null);

Querying the table shows the NULL row:

select * from test_null;
+----+------+
| id | name |
+----+------+
|  1 | zlm  |
|  2 | NULL |
+----+------+

Using = NULL returns an empty set, while IS NULL correctly retrieves the row:

select * from test_null where name = null;   -- empty set
select * from test_null where name IS NULL; -- returns row with id=2

The null‑safe operator <=> returns true when both operands are NULL:

select NULL <=> NULL; -- true

Aggregates treat NULL specially: COUNT(*) counts all rows, but COUNT(name) ignores NULL values.

select count(*), count(name) from test_null;
+----------+-------------+
| count(*) | count(name) |
+----------+-------------+
|        2 |           1 |
+----------+-------------+

When using DISTINCT, GROUP BY, or ORDER BY, MySQL considers all NULLs as the same value, which can affect result sets and sorting order.

insert into test_null values (3, null);
select distinct name from test_null;
+------+
| name |
+------+
| zlm  |
| NULL |
+------+

Unlike Oracle, MySQL can use indexes on columns that contain NULL values. The following EXPLAIN output shows index usage for a query on a nullable column:

explain select id, k from sbtest1 where k IS NULL;
+----+-------------+---------+------------+------+---------------+------+---------+------+-------+----------+--------------------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+---------+------------+------+---------------+------+---------+------+-------+----------+--------------------------+
|  1 | SIMPLE      | sbtest1 | NULL       | ref  | k_1           | k_1  | 5       | const|    1 | 100.00   | Using where; Using index |
+----+-------------+---------+------------+------+---------------+------+---------+------+-------+----------+--------------------------+

Each NULL value occupies an extra byte in the row to store the null flag, increasing storage overhead.

Null value always leads to many uncertainties when disposing sql statement. It may cause bad performance accidentally.

Because of these drawbacks, the article recommends defining columns with NOT NULL and using sensible defaults such as 0 for numeric types or empty strings for character types, and applying IFNULL() only when necessary to keep SQL statements deterministic.

As these above drawbacks, it’s not recommended to define columns with default NULL. We recommend defining “NOT NULL” on all columns and using zero number & vacant string to substitute relevant data type of NULL.