2

The MySQL manual states that a field with the type date takes up three bytes, but if the date is 0000-00-00, does it still take up those bytes? If so, is there an advised method to reduce storage, such as setting the field to NULL?

Kohjah Breese
  • 4,008
  • 6
  • 32
  • 48
  • 1
    Do you really need to save those bytes? – Oliver Charlesworth Aug 12 '14 at 22:34
  • 2
    you have to use all three bytes. thats the same as you would type xxxx-xx-00, you don't know the first ones, if you don't write the 0000- – Raphael Müller Aug 12 '14 at 22:35
  • possible duplicate of [NULL in MySQL (Performance & Storage)](http://stackoverflow.com/questions/229179/null-in-mysql-performance-storage) – asmecher Aug 12 '14 at 22:36
  • @Oli, Not really. I just like to save bytes, esp. as I am working with tables in the 10s of GBs. – Kohjah Breese Aug 12 '14 at 22:36
  • 2
    Why would you want to store an invalid date in the first place? What's wrong with using `NULL` for what it was meant to: store the absence of information. –  Aug 12 '14 at 22:55
  • @a_horse_with_no_name: That is essentially what I am asking. When there is no date to be stored in a date field, is it a good idea to use NULL? Will this cause performance issues? Will this be best for minimising use of disk space? – Kohjah Breese Aug 12 '14 at 23:01

1 Answers1

4

InnoDB (which you should be using; do not use MyISAM) uses zero bytes for a field that is NULL, but the full number of bytes for a zero value date.

Using NULL may allow InnoDB to store a few more rows per page. I say may because you might have a bunch of other non-null fields per row, so the ratio of savings will be small. If you can do this, InnoDB can fit more rows in the same size buffer pool, thus incur less frequent I/O to read pages (because they stay in the buffer pool), thus get more performance.

Those are a lot of conditions and caveats. The net benefit to performance is likely to be very modest.

I suggest this should not be the focus of your optimization efforts. You'll get better bang for the buck by concentrating on:

  • Analyzing queries so you can choose the right indexes.
  • Use Memcached for caching on a case-by-case basis in your app.
  • Design application architecture for better scaling.
  • Upgrade your system RAM and buffer pool size, until it holds more of your database pages.
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828