Geek Answers Handbook

Space occupied by a TEXT column in MySQL

I am aware that TEXT in MySQL can have a maximum length of 65535 characters.

Now I create a table with one of the columns as TEXT and do not specify the length. Because of what it will take the maximum length as 65535 defualt.

However, if one of my records has a value for a TEXT column with a length of only 10 characters, will the space allocated for 65535 characters be reserved, or will MySQL just use the memory required for only 10 characters?

+5
source share
2 answers

For a TEXT data type, it will be similar to the actual length in bytes of the string value + (2 bytes)

The manual says:

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

VARCHAR, VARBINARY, and the types BLOB and TEXT have variable length types. For each storage requirement, these factors depend on:

  • Actual column value length
  • Maximum column size
  • The character set used for the column because some character sets contain multibyte characters

enter image description here

+7
source

In SQL, TEXT is usually a special case. Fields are usually stored on disk to facilitate access and indexing, but TEXT and BLOBS are usually stored (and ignored) in a separate block of disk space with only the offset stored in the row. Therefore, there is no need to allocate additional space. Updating the value simply leads to the only update of the offset pointer in the storage area of ​​the main table and saving the current value in the block - there is practically nothing that could be improved in performance when reserving enough space for the maximum value. Almost certainly, the actual value is stored to occupy only the necessary space at any given time. Varchars, on the other hand, have traditionally been kept within the actual range and therefore allocated the maximum amount of space. Perhaps this has changed since their size has grown to 16 bits or not.

+1
source

More articles:


All Articles