Datatype sizes and storage in mysql

Question

I'm a begginer using mysql datatbase. I'm trying to save user data in a large Json file which in worst case will have a size around 5Mb its obvious that I should use LARGE TEXT, but I wanted to know what happens to the rest of 11Mb. Is it wasted?

Answer 1

No, MySQL only stores the data you need on a case by case basis. That is, if your JSON is 5MB on one row, you need to use MEDIUMTEXT but it only stores 5MB, not 16MB. If the JSON is only 42KB on the next row, that will only store 42KB. It's variable storage.

Well, there's a little bit of "rounding up" of space, because the space is allocated in whole 16KB pages up to half a MB, then 1MB at a time for BLOB/TEXT documents over one half MB. But I wouldn't worry about this.

All the variable-length data types in MySQL work this way. VARCHAR, VARBINARY, TEXT, BLOB, and all the different types of TEXT and BLOB.

Only CHAR and BINARY store the full length even if you don't need it. So those types are best used when all your strings are going to be the same length.

Answer 2

For data type storage in INNODB database you can refer tho this mysql doc link https://dev.mysql.com/doc/refman/5.7/en/storage-requirements.html essentially you can store data with these dimension assuming L is your field/file dimension

TINYBLOB, TINYTEXT    L < 2^8 ( 2^8 - 1 byte)
BLOB, TEXT            L < 2^16 ( 2^16 - 2 bytes)
MEDIUMBLOB, MEDIUMTEXT   L < 2^24 (2^24 -3 bytes)
LONGBLOB, LONGTEXT    L < 2^32 (2^32 - 4 bytes)

so for your 11 MB files you can use MEDIUMTEXT again