Menu
Does making a datum NULL save any memory versus its normal size?
292
A NULL value in databases is a system value that takes up one byte of storage and indicates that a value is not present as opposed to a space or zero or any other default value.
Using Sparse Columns, NULL value will not consume any space regardless of using fixed-length or variable-length columns.
It is very important to understand that a NULL value is different than a zero value or a field that contains spaces, spaces are considered as values because they are strings (and sql can't tell what the value of a string means to the user per se), but a NULL signifies a missing value, and hence has no value associated ...
Since MySQL 5.0. 3 InnoDB uses COMPACT row format which uses only one bit to store a NULL (of course one byte is the minimum), therefore: Space Required for NULLs = CEILING(N/8) bytes where N is the number of NULL columns in a row.
This is discussed in the MySQL manual chapter on Storage Requirements. There is no simple answer; it depends on the data type of the column, whether the column is indexed; and the storage engine. The impact of using NULL for a column can vary from nothing to several bytes (depending on how many other columns are also NULL-able.)
The storage impact of declaring a column as accepting NULL, and of actually storing a NULL value, is probably minor. Having a column with lots of NULL values often indicates a need for (further) normalization.
The basic rule is, design your schema based on the properties of the data, not on the storage impact. Fix things only if they turn out to be a problem.
80
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
