Sahee Sahee - 1 year ago 87
MySQL Question

MySQL TEXT memory allocation

Anybody knows how MySQL allocates disk space for fields like "TEXT" or "BLOB"

For example, what happens when I insert 10kb string into "TEXT" column? Is the entire 65kb data allocated or only 10kb?

Answer Source

This is explained in the documentation:

BLOB, TEXT              L + 2 bytes, where L < 2^16
MEDIUMBLOB, MEDIUMTEXT  L + 3 bytes, where L < 2^24 
LONGBLOB, LONGTEXT      L + 4 bytes, where L < 2^32

Variable-length string types are stored using a length prefix plus data. The length prefix requires from one to four bytes depending on the data type, and the value of the prefix is L (the byte length of the string). For example, storage for a MEDIUMTEXT value requires L bytes to store the value plus three bytes to store the length of the value.

So in short, the whole 65kb is not wasted.