Understanding MySQL Field Attributes: NULL, NOT NULL, and AUTO_INCREMENT Performance Considerations

MySQL columns have many attributes that can significantly affect performance, so it is worthwhile to explore them.

1. NULL / NOT NULL – External programs treat NULL as an unknown value, and many development standards require columns to be NOT NULL. Converting NULL to meaningful values improves readability, maintainability, and simplifies SQL.

Key characteristics of NULL include:

Any expression involving a NULL column results in NULL, which can lead to unexpected results when concatenating strings.

Example of unexpected NULL concatenation:

mysql> show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
  `r1` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Correct handling uses IFNULL:

mysql> insert into t3 select concat(ifnull(r1,''),'database') from t1 limit 2;

The resulting rows contain the string database instead of NULL.

2. COUNT with NULL columns – Counting a column that contains NULL values yields a lower count because NULLs are ignored.

mysql> select count(r1) as rc from t1;   -- returns 16384
mysql> select count(r1) as rc from t2;   -- returns 32768

Using IFNULL or COUNT(*) produces the correct total:

mysql> select count(ifnull(r1,'')) as rc from t1;   -- returns 32768
mysql> select count(*) as rc from t1;          -- returns 32768

3. Indexes on NULL columns – Indexing a column that allows NULL consumes an extra BIT (shown as an extra byte in key_len).

mysql> explain select * from t1 where r1 = ''\G
key_len: 43
mysql> explain select * from t2 where r1 = ''\G
key_len: 42

Only InnoDB, MyISAM and Memory engines support indexes on NULL columns in MySQL 8.0.

4. AUTO_INCREMENT

Auto‑increment columns are controlled by three lock modes: innodb_autoinc_lock_mode=0 – traditional mode, holds a table‑level lock for the whole statement. innodb_autoinc_lock_mode=1 – consecutive mode, holds a lightweight thread‑level lock only while allocating IDs. innodb_autoinc_lock_mode=2 – interleaved mode, no table lock, highest performance but not safe for statement‑based replication.

Example of lock contention in mode 0:

-- SQL 1
insert into f1(c2) select rpad(uuid(),100,uuid()) from t1;   -- long running, holds lock
-- SQL 2
insert into f1(c2) select 'database';                     -- times out with ERROR 1205

Mode 2 allows concurrent inserts without timeout.

Additional AUTO_INCREMENT considerations:

Variables auto_increment_increment and auto_increment_offset control step size and offset, useful in master‑master or multi‑source replication.

To obtain the latest inserted value reliably, use SELECT MAX(c1) FROM f1 instead of LAST_INSERT_ID() for multi‑row inserts.

When the column reaches its data‑type maximum (e.g., TINYINT max 127), further inserts cause overflow errors; plan the column type accordingly.

Signed values can be inserted explicitly into an AUTO_INCREMENT column, but inserting NULL after reaching the maximum will cause duplicate‑key errors.

Summary – Prefer NOT NULL for columns, handle NULLs with functions like IFNULL, be aware of how NULL affects COUNT and indexes, and choose the appropriate AUTO_INCREMENT lock mode and configuration to balance safety and performance.