0

I have a table with lots of default values, and want to store very common values as NULL to save space, without forcing my SQL statements or application logic to become overburdened with conditionals.

Ideally, I would like this equivalent of an INSERT and UPDATE trigger:

if (NEW.field_a = 'SomeDefault')
    NEW.field_a = NULL;

With the ability for this query to return results:

SELECT * FROM table WHERE field_a = 'SomeDefault'

And the ability for this result to be > 1:

SELECT field_a, count(1) FROM table GROUP BY field_a HAVING field_a = 'SomeDefault'

And for this join query...

non_optimized
|| w          || x   ||
| SomeDefault | foo  |
| OtherValue  | bar  |

optimized
|| y          || z   ||
| NULL        | 1.1  |
| OtherValue  | 2.2  |

SELECT * FROM non_optimized JOIN optimized ON w = y;

... to produce this output:

| SomeDefault | foo | SomeDefault | 1.1 |
| OtherValue  | bar | OtherValue  | 2.2 |

Can some combination of triggers, or table config, accomplish these requirements?

Chris Betti
  • 477
  • 1
  • 5
  • 11
  • 2
    I'd reconsider whether the space that can be saved using this approach is really worth the effort and obfuscation of the data. Perhaps more normalization would accomplish what you want. – dartonw Jan 23 '15 at 00:52
  • This sounds a really bad idea. Do you have specific problems that you plan to solve this way? It would definitely not decrease complexity. Also, you would lose the ability of creating good indexes, introduce the need of querying for the default data in basically all queries and so on. – András Váczi Jan 23 '15 at 10:21
  • I'm solving the problem of growing join tables with a few columns of additional state tied to them (for example, relationship type, relationship active). The largest of these join tables is 10GB and growing at 20% per month. Until we can move historical relationships into a better structure, I'm looking for a way to reduce table size. – Chris Betti Jan 23 '15 at 13:35
  • Maybe you could achieve some result with the help of functions NULLIF and either IFNULL or COALESCE, but the way I imagine you would likely use them you would certainly kill the performance. Consider rewriting your question by adding the underlying problem to it (presumably the one you've mentioned in your comment, except you may want to elaborate on it a little, as it doesn't seem very clear at the moment). – Andriy M Jan 24 '15 at 13:21

0 Answers0