I need to store a DATETIME value that may or may not have a date.
My options are:
| starts DATETIME | |---------------------| | 0000-00-00 12:38:06 |
| starts_date DATE | starts_time TIME | |------------------|------------------| | NULL | 12:38:06 |
DATE value takes 0 storage, I may save a few bytes on most records (8 bytes on full
DATETIME vs 3 bytes on just
TIME). However, the following pages say that
NULLs still take as much storage as the actual values on the fixed length columns, such as
VARCHAR. Is the
DATE considered fixed-length too?
- The byte size of NULL Decimal(19,4) or DATETIME (this is for SQL Server though)
- Does an empty column value occupy same storage space as a filled column value
If you are using MyISAM, don’t.
If you found that
DATETIME is 8 bytes, you are using an old version of MySQL; it takes only 5 bytes now. Upgrade.
“Fixed” is slightly important for MyISAM; rarely important for InnoDB. So don’t worry. In particular, don’t worry unless you have a billion rows.
For InnoDB Tables
Helpful Notes About NULLs:
For the third row, I inserted NULLs in FIELD2 and FIELD3. Therefore in the Field Start Offsets the top bit is on for these fields (the values are 94 hexadecimal, 94 hexadecimal, instead of 14 hexadecimal, 14 hexadecimal). And the row is shorter because the NULLs take no space.
For MyISAM table
you need at least some space for thge header, but it should be very small