CODEWITHSUNDEEP
mysqloptimizationsqldatatypes
user5137948
user5137948

Reputation:

In MySQL, does LONGTEXT waste space when only TEXT is necessary?

I have a column, its datatype is LONGTEXT. While TEXT datatype is enough for 95% of the values. I need LONGTEXT just for 5% of values.

Now I want to know, storing a small value in the LONGTEXT will waste a lot of space? Is that optimize?

Upvotes: 6

Views: 2481

Answers (1)

Evgeny
Evgeny

Reputation: 4010

Lets look at MySQL documentation.

TEXT[(M)] [CHARACTER SET charset_name] [COLLATE collation_name]

A TEXT column with a maximum length of 65,535 (2^16 − 1) characters. The effective maximum length is less if the value contains multibyte characters. Each TEXT value is stored using a 2-byte length prefix that indicates the number of bytes in the value.

LONGTEXT [CHARACTER SET charset_name] [COLLATE collation_name]

A TEXT column with a maximum length of 4,294,967,295 or 4GB (2^32 − 1) characters. The effective maximum length is less if the value contains multibyte characters. The effective maximum length of LONGTEXT columns also depends on the configured maximum packet size in the client/server protocol and available memory. Each LONGTEXT value is stored using a 4-byte length prefix that indicates the number of bytes in the value.

MEDIUMTEXT [CHARACTER SET charset_name] [COLLATE collation_name]

A TEXT column with a maximum length of 16,777,215 (2^24 − 1) characters. The effective maximum length is less if the value contains multibyte characters. Each MEDIUMTEXT value is stored using a 3-byte length prefix that indicates the number of bytes in the value.

So the difference is 2 bytes for prefix.

Upvotes: 6

Related Questions