Re:Why we're ending support for MySQL
by pizza234 on Wednesday July 03, 2019 @09:15AM
(#20343997)
There have always been low-level differences in the way MySQL treats the two (VARCHAR <> TEXT/BLOB) data types, although some have been removed over the time.
A notable example is that before version 8, internal temporary tables didn't support BLOBs when in-memory¹, so that they would swap to disk, causing a performance penalty.
Regarding the length, an important concept is that it can be used for optimizations.
InnoDB adds a certain number of bytes for each value stored, depending on the size. For example, a TINYTEXT consumes 3 bytes less than LONGTEXT for the same value.
Another difference is the location of the data. TEXT/BLOBs up to 40 bytes long will be stored in line, while the limit is 768 bytes for VARCHARs. A 100 bytes long TEXT will require one page access more than the corresponding VARCHAR.
Of course, it's up to the engineer to choose whether to consider optimizations like this or not, but as a matter of fact, those differences exist.
¹=note that this concept is different from tables backed by the `MEMORY` engine, and different from user temporary tables.
A notable example is that before version 8, internal temporary tables didn't support BLOBs when in-memory¹, so that they would swap to disk, causing a performance penalty.
Regarding the length, an important concept is that it can be used for optimizations.
InnoDB adds a certain number of bytes for each value stored, depending on the size. For example, a TINYTEXT consumes 3 bytes less than LONGTEXT for the same value.
Another difference is the location of the data. TEXT/BLOBs up to 40 bytes long will be stored in line, while the limit is 768 bytes for VARCHARs. A 100 bytes long TEXT will require one page access more than the corresponding VARCHAR.
Of course, it's up to the engineer to choose whether to consider optimizations like this or not, but as a matter of fact, those differences exist.
¹=note that this concept is different from tables backed by the `MEMORY` engine, and different from user temporary tables.