Is there any reason to ever use TINYTEXT?

Question

As far as I can tell, VARCHAR(255) does everything TINYTEXT can do. VARCHAR has the same storage size, it's part of the ISO standard, it allows default values other than null. I can't see any reason to ever use TINYTEXT. But it exists, so maybe there's a reason.

Is there any situation where TINYTEXT would be the preferred data type for size, speed, or other considerations?

Answer 1

The only situation I can think of where TINYTEXT might be helpful is if you have really large rows. The contents of VARCHAR columns counts against the maximum row size of 65,535 bytes, but TEXT and BLOB data does not; a TINYTEXT column only adds 1 byte to the row size.

Actually, for InnoDB tables, things are somewhat more complicated. InnoDB storage is described in the following pages:

https://dev.mysql.com/doc/refman/5.7/en/innodb-physical-record.html https://dev.mysql.com/doc/refman/5.7/en/innodb-row-format-dynamic.html https://dev.mysql.com/doc/refman/5.7/en/innodb-compression-internals.html

To summarize, the data stored in the part of the table subject to the above limit for TINYTEXT consists of a 1-byte length field and a 20-byte pointer to the external data, so a TINYTEXT adds 21 bytes to the row length. However, when an external value is less than 40 bytes, the data is stored inline in the row. So for a TINYTEXT less than 40 bytes, the storage is similar to VARCHAR(255), and it's all counted against the row length limit.

Answer 2

TINYTEXT is essentially useless. It probably exists for consistency (4 sizes of TEXT and BLOB). TINYTEXT existed in MySQL long before VARCHAR could have more than 255 characters and before a character could be more than one byte.

TINYTEXT actually has disadvantages over VARCHAR. A complex SELECT may need to create a tmp table (eg, for ORDER BY); the first preference is to use MEMORY. The fallback is the less efficient MyISAM. Any size TEXT and BLOB forces going straight to MyISAM.

A difference is that the number in VARCHAR is characters; the TEXT sizes are measured in bytes. A utf8 character can occupy up to 3 bytes.

Bottom line: Don't use TINYTEXT. (Or TINYBLOB, which can be replaced by VARBINARY(...).)