MEDIUMTEXT vs VARCHAR(65535), and TINYTEXT vs VARCHAR(255)

MySQL & MariaDB Other MySQL® Questions
1

I understand the *TEXT fields are stored “out of row”, which I imagine is a sort of internal normalization with a separate “hidden” table for each text column, joined by some hidden ID.

That sounds very efficient, especially when you have a significant portion of queries in your normal workload that don’t retrieve the text columns. That would be true in theory, but does MySQL 5.0.x actually perform this optimization?

Also, that being the case, why would anyone ever want to use VARCHAR, when given the choice?

Sometimes there is no choice, e.g. HEAP storage engine, and some use cases involving specific features that balk at text columns.

I guess one case is when you need to support workloads that consist mostly of “SELECT *” queries, uh?

Am I missing anything…

Thanks

2

The memory storage engine does not support text. Hence if you have a table that needs sorting without the ability of using an index, the sorting is always done on disk no matter the size of your sort buffer if you have a text column in your result set.