0

I have an InnoDB table in MySQL (version 8.0 if it's relevant), it has a column with decimal(15,5) type.

I was wondering, if I set the default value of this column to NULL it will actually still take the default decimal storage (as long as it stays NULL) in each row?

I do understand that storage is cheap, but please kindly help me with facts here.

P.S. I've already read this question, but it is very old (MyISAM era) and doesn't really answer my question accurately.

J. Doe
  • 812
  • 1
  • 15
  • 33
  • 3
    It does answer your question, just you may not like it: `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.` Any data savings would be minimal and disk space is really cheap nowadays. Null and 0 have different meanings, you should use the one that fits your model. – Shadow Mar 21 '21 at 22:08
  • If the default is null, you will need to ensure that it is handled correctly in the code everywhere this field is used. However, trying to save a few kilobytes of disk space is not going to make any difference unless each cell in the RAM is made up of gold in your database server. – Allen King Mar 21 '21 at 22:12
  • @Shadow That is just the guy's opinion, I'm not asking for opinions, but if in fact setting NULL saves space in InnoDB decimal columns, thanks for closing the question I guess... :) – J. Doe Mar 21 '21 at 22:13
  • @AllenKing If adding a few simple lines of code will save hundreds of megabytes in the long run, I'll rather do it! I'm not worried about the storage on the server itself, but backing up the database regularly. – J. Doe Mar 21 '21 at 22:16
  • 1
    That's not an opinion, that's a design principle ("premature optimization is the root of all evil"). What you should be thinking is: does this decimal field need to be nullable. – Shadow Mar 21 '21 at 22:19
  • @Shadow, The irony... if I ask for an opinion here, my question will get closed for being opinion based! but now that I ask for facts, I'm forwarded to a 10-year old opinion that has nothing to do with my question... – J. Doe Mar 21 '21 at 22:20
  • 1
    Apart from the design principle, the answer accurately says that the question cannot be accurately answered in general and points to the mysql manual page (redirected to the appropriate v8.0 page by mysql), which contains the necessary information to calculate any savings if you wish to do so. Innodb has multiple different row formats that can again influence if there is a storage difference. – Shadow Mar 21 '21 at 22:25
  • 3
    I agree with @J.Doe that an answer should answer the question asked, not smack the OP for asking a "wrong" question. The question is still a duplicate, but I've have changed the link to a different past question. – Bill Karwin Mar 21 '21 at 22:31
  • 1
    @BillKarwin Noone was smacking the OP for asking the wrong question! The OP just did not like the answer already given. Your own answer has exactly the same conclusion: do not worry much about nulls, focus your efforts somewhere else. (Btw, not sure if there is any savings for a fixed-width field, like decimal at all) – Shadow Mar 21 '21 at 22:40

0 Answers0