Menu
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
624
In most circumstances, VARCHAR provides better performance, it's more flexible, and can be fully indexed. If you need to store longer strings, use MEDIUMTEXT or LONGTEXT, but be aware that very large amounts of data can be stored in columns of these types.
TEXT is a string data type that can store up to 65,535 characters. TEXT is commonly used for brief articles. LONGTEXT is a string data type with a maximum length of 4,294,967,295 characters.
LONGTEXT can store the maximum characters among all four, up to 4,294,967,295 characters i,e 4,294,967,295 bytes or 4GB. This is more than enough storage for any long-form text strings. For example, a book that MEDIUMTEXT can't hold can be stored using LONGTEXT. LONGTEXT takes 4-Bytes overhead.
What will happen if the data being loaded into a text column exceeds the maximum size of that type? Explanation: Mysql cannot allocate dynamic memory therefore if data exceeds the memory then extra data will be truncated.
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.
74
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
