0

In my DB, I have currently float data. All column are set to be NULLABLE and when value is missing I put there NULL.

My DB is too big and if I know, that values are in range 0 - 100 they can be rounded to 1 decimal place. So using float is overhead and I am thinking of use smallint (multiply every float by 10 and store it as rounded number). Now, what about NULL values. I have two options:

  1. still use NULL

  2. use some "out of bounds" value, like 9999, to represent NULL (and also make this value default, when nothing is set for column). However, in my queries, I need to do this:

    SELECT AVG(NULLIF(data, 9999)) AS data, ....

    (When I use NULL, i can just use AVG(data), while NULL values are not computed..)

What is better to use. Or is there a better technique?

NDM
  • 6,731
  • 3
  • 39
  • 52
Martin Perry
  • 9,232
  • 8
  • 46
  • 114
  • What do you mean by "my database is too big". Query slowdowns? Disk space requirements? – Daniel Schneller Aug 02 '13 at 08:26
  • @DanielSchneller Disk space requirements. With change of datatype, I can save half of space and also do better searching (I can in compare int, but not in float) – Martin Perry Aug 02 '13 at 08:28

4 Answers4

2

Why would you try to "roll your own" NULL functionality if it already exists? As you describe it, your usage of NULLs is correct and perfectly valid. I don't see any advantage you'd gain by using a magic number as an artificial NULL replacement; you'd just introduce the possibility for errors.

TL;DR: Use NULL.

Carsten
  • 17,991
  • 4
  • 48
  • 53
1

The NULL value takes the exact same space than a value on a fixed field (float, int...). You can't optimize the space use by not using NULL, or whatever. Sorry :)

Maxime Lorant
  • 34,607
  • 19
  • 87
  • 97
  • I am otpimizing space by change float to smallint. Quastion about NULL is just for performance reason and desing of table (what is better to use) – Martin Perry Aug 02 '13 at 08:24
  • 1
    In that case, still use NULL. It's better than make implicit rules about your data structure. It also avoid to do a `IFNULL()` in your queries... – Maxime Lorant Aug 02 '13 at 08:25
1

Why would you use valid data to represent NULL if you have the opton to actually use NULL itself?

I do not see any benifit

NDM
  • 6,731
  • 3
  • 39
  • 52
  • I thought, maybe datatype cohherence. I am using the same approach (with 9999) in C++, because there is no way, to put NULL into primitive datatype. – Martin Perry Aug 02 '13 at 08:26
  • Thanks... I know it can be done, but I am not sure with performance over classic datatype. I will give it a try and I will see :) – Martin Perry Aug 02 '13 at 08:34
0

When you are using NULL means that you set this column as 'nothing', in the second case you give your column a value that represents the NULL.

So in first your don't have set your column and at second you set a value that shows it is NULL.It depends on what you want to do. If you want to be commitment in your db on that column use the second, else if you want just to be empty unless you fill it use NULL .

Mhche
  • 143
  • 14