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?


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.

  • If I may opine here: Can we all stop saying "it won't matter unless..." Unless could happen due to future changes additions, etc. As programmers we should ALWAYS be looking for the most efficient code both in terms of performance and storage for any application that is going to be used in a production environment. jus sayin!
    – John
    Sep 30 at 15:39
  • I should have said that Fixed is worse for performance even in MyISAM. But then I would have to qualify it with "in most cases". It would take several paragraphs to itemize the cases. I'll be happy to critique a particular CREATE TABLE.
    – Rick James
    Sep 30 at 21:16
  • @John - Also, I have a Rule of Thumb: "If the proposed 'improvement' doesn't save at least 10% (of performance/space/whatever), then move on." That is, it is inefficient in the long run to spend too much time on small fixes.
    – Rick James
    Sep 30 at 21:19

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.