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         |

If a NULL 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 CHAR vs. VARCHAR. Is the DATE considered fixed-length too?

  • 1
    You should NOT use "magic" dates (or "magic" anything else) - if NULLs are appropriate, then use them! – Vérace May 8 at 12:47
  • @Vérace Hi, thank you for commenting. By "magic" you mean using '0000-00-00' in a combined column instead of splitting the value into two columns, and using the actual NULL in the date one, correct? – aexl May 8 at 18:40
  • 1
    Yes - '0000-00-00' will only serve to confuse the optimizer which will assume that it's a valid value - whereas with NULL, it won't. – Vérace May 8 at 19:01

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.

See https://dev.mysql.com/doc/internals/en/innodb-field-contents.html

For MyISAM table

you need at least some space for thge header, but it should be very small

see the exact https://dev.mysql.com/doc/internals/en/myisam-introduction.html

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.