What is the disadvantage to using a MySQL longtext sized field when every entry will fit within a mediumtext sized field?

Question

What is the disadvantage to using a MySQL longtext sized field when every entry will fit within a mediumtext sized field?

The reason I am asking is because I have some longtext sized fields and recently realized they are much too large, but then struggled with motivating myself to make the minute change because "what's the harm?" hence the question.

Circumfuse

Answer 1

The only storage size difference is the number of bytes allocated for the "how many bytes is this field" number. From the fine manual:

TINYTEXT    L + 1 bytes, where L < 2^8  
TEXT        L + 2 bytes, where L < 2^16
MEDIUMTEXT  L + 3 bytes, where L < 2^24
LONGTEXT    L + 4 bytes, where L < 2^32

So a longtext will use one more byte than mediumtext to store the same text. There is a bit more information in the Data Type Storage Requirements section of the manual and some more in the The BLOB and TEXT Types section.

There's no practical difference between the four TEXT types.

Osugi

This is what I had assumed. The only real difference would be the use of 1 extra byte of space per row. Is that correct? If so, I can't imagine why there are any field options other than longtext? – Circumfuse

@darkAsPitch: AFAIK the 1 extra byte is the only difference. There are probably four types for historic reasons. – Osugi

Is there no read/write performance difference between mediumtext and longtext, but only storage? If so, 4 bytes don't matter and you should always prefer longtext to mediumtext? – Forsberg

With multiple types (and not only longtext) you can ensure the DB / storage won't be overwhelmed by a bug / malicious input. – Inhibition

Answer 2

MySQL uses temporary tables a lot of times to execute queries. So the disadvantage of having large sized field is that MySQL will reserve that much space for the column.

You will have slow execution with same data if you have that column marked as longtext compared to mediumtext.

Nystatin

But the MySQL mediumtext and longtext column types are variable length, the only difference between them is the number of bytes allocated for the column size. – Osugi

If an internal temporary table is created initially as an in-memory table but becomes too large, MySQL automatically converts it to an on-disk table. dev.mysql.com/doc/refman/5.5/en/internal-temporary-tables.html – Intercellular

LONGTEXT is 4 GB. Does Mysql reserve 4 GB of space (per row) in temporary tables when a LONGTEXT column is involved in the query? That seems unworkable when more than a few rows are involved. – Mulish