Re: TINY TEXT vs Varchar(240) and TEXT vs Varchar(300-500)

[Rick James]

Executive summary...
** Do some vertical partitioning, it's the "right" thing.
** Don't worry about space; your concerns are in the noise.

Details:

1. VARCHAR up to 255 and TINYTEXT are virtually identical. Each take 1 byte for length, plus the actual length.

2. VARCHAR 256..65536 (if you have a newer version) and TEXT use a 2-byte length. You pay 1 byte, but get a huge increase in the max allowed.

3. Build parallel tables (Vertically partition).

4. Fat columns (eg, TEXT, if they tend to be big) should be in a parallel table for performance reasons, anyway.

5. If some of the fields are missing (null) for a bunch of the rows, they make a good candidate for splitting off -- the extra table would have fewer rows. You would use LEFT JOIN to put things back together when needed.

6. A row can be wider than 65K bytes -- the limit you found has to do with the definition of the table. The column _names_, plus ENUM values, plus other stuff, is limited to 65K.

7. The *3 you mention is probably the _potential_ width of utf8 strings. A utf8 _character_ is 1-3 _bytes_. A 500 _byte_ TEXT field will take 502 bytes. A 500 _character_ TEXT field will take at least 502 bytes, 1502 in the worst case.

8. More minor issues: Consider TINYINT/SMALLINT/MEDIUMINT/BIGINT (1/2/4/8 bytes) for numbers. And use UNSIGNED where appropriate.

9. INDEXes are limited to 768 or 1000 bytes. INDEXes cannot have TEXT, but can have VARCHAR. These notes _may_ impact the field definitions.