Memory consumption of having a column LONGTEXT in MySQL database

Question

I'm creating a log table in my MySQL database. One of the field will be only used in approximately 5% of the logs and will contains stack traces and others lengthy informations for the developers. I was considering using the LONGTEXT field but I was wondering if using this would make my database grow very quickly, even if this column is empty in 95% of the rows.

So my question in clear, is there a memory consumption of having a LONGTEXT column even when this column is empty in most of the rows? For example, if I use TEXT instead and truncate the strings that are too long, would I save a lot of space on the database.

It's important to add that there will be a LOT of logs over time.

Thanks!

Answer 1

Although it varies slightly from one difference of MySQL to another, in general terms BLOB-type columns like LONGTEXT are inherently variable length and take up almost no storage when not used. The space needed by them isn't even allocated in the case of a NULL value.

As for your concerns about scaling, there's only one way to find out: Generate a gigantic amount of log data to test any schema you're intending to use. Push it so full that it nearly dies, and then get some metrics on how much it can handle. That'll give you an idea of how viable your approach is.