#StackBounty: #mysql #database-design #performance #storage Replace column by LEFT OUTER join to save space

Bounty: 50

I have a table t1 with 100M row. For column A, 95% of the rows have value NULL0, the other records have values from 1 to 9.

To shrink my table, I’m thinking about moving column A to table t2. t2 would contain only rows where A is not NULL0. The “original” table would then be created by doing a LEFT OUTER JOIN between t1 and t2.

Questions:

  • Is this a common approach?
  • Is there a name for this pattern?
  • Are there any downsides except that I have to handle two tables now in my application code?

(I use MySQL, but I guess it doesn’t matter so much for this question.)

Update: As it turns out, NULL was not a good choice for my example (since depending on db engine, it might not need any storage at all). I rewrote the first sentence of my question to make it more clear what I’m asking for.


Get this bounty!!!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.