MySQL database design with sparse column

user_5153690 0
Viewed 2062

I have a table (millions of rows) where one of the columns is a Text field (stores json blobs). But only about 10-20% of them are actually non-Null. What is the best practice when it comes to sparse columns? Should I

a) Just keep the table as is or

b) Create a new table with just that Text column?

If I am not mistaken, option (a) is fine because InnoDB will dynamically only allocate as much space that is needed for that Text column right? Is there any reason to go with option (b)? Seems like option (b) will just add complexity in querying (joining) these tables and further increase the space complexity as well.

database-design mysql database
3 Answers

MySQL (InnoDB storage engine) stores nothing for a NULL. Well, each row has a bitfield with 1 bit for each nullable column. The bitfield is followed by data values for non-NULL columns. And variable-length columns like VARCHAR, TEXT, BLOB, or JSON take only the space needed given their length.

So I'd suggest keeping your table as is, keep the TEXT field in the table, and make it NULL when there's no JSON data.

P.S.: Aren't you using the JSON data type?

Bill_Karwin_208600

You mentioned the storage/space consideration. I think most importantly is how you will use the data. If you performance is okey with doing a like "%% match, then just leave it.

Denormalize the data allow you better query/index the content.

user_103559490

In general, it does not matter whether you do (a) or (b). But here are some more considerations:

  • If you do SELECT * but ignore that column, then (a) is wasteful.
  • Certain InnoDB ROW_FORMATs will put 'short' strings in the table, not separate; others will store them in a separate block, leaving behind 20 or 767 bytes in the main block. (It gets rather tedious and confusing to see if this will really matter for (a).)
  • (b) involves a LEFT JOIN in your code when you do want the column. You may consider this a bother.
user_17668310
Related