CODEWITHSUNDEEP
mysqldatabasefield
Jane Wilkie
Jane Wilkie

Reputation: 1743

MySQL Text Types and using a size bigger. Is it efficient?

I'm designing a database to store documents. Some of these documents clock in at just over 64kb and none would be larger than 128kb. It appears that my only recourse is to specify my texttype as MEDIUMTEXT which has a maximum size of 16MB. That just seems like overkill for what I am doing especially since I'm storing probably a hundred documents a day. I guess the question I have is whether or not I'm going to experience a performance hit since MEDIUMTEXT is so much bigger than TEXT. I don't know much about the MySQL internals to know if there's some weird allotment issue on disk or memory that will come back to bite me. Of course we could just do it all in something modern like MongoDB but I can't get a sign off on that. :-(
In case someone else is grappling with this in the future, I thought I'd note the text types in MySQL and their maximum sizes for reference.

TINYTEXT 256 bytes
TEXT 65,535 bytes ~64kb
MEDIUMTEXT 16,777,215 bytes ~16MB
LONGTEXT 4,294,967,295 bytes ~4GB

Any advice anyone has would be appreciated. Janie

Upvotes: 9

Views: 6591

Answers (1)

Joachim Isaksson
Joachim Isaksson

Reputation: 180917

From MySQL's documentation on storage requirements it looks like you don't need to worry too much about using a longer text type;

L represents the actual length in bytes of a given string value.
...
For example, storage for a MEDIUMTEXT value requires L bytes to store the value plus three bytes to store the length of the value.

In other words, mediumtext does not require the full max length for each value stored, it just requires the actual length of the data you want to store + 3 bytes.

Upvotes: 16

Related Questions