Re: VARCHAR vs. TEXT - some performance numbers

[Rick James]

There are reasons for VARCHAR(255) being better than TEXT. (Your test did not find one, as was pointed out.)

When a tmp table is needed for a SELECT, the first choice is to use MEMORY, which will be RAM-only, hence probably noticeably faster. (Second choice is MyISAM.) However, TEXT and BLOB are not allowed in MEMORY, so it can't use it. (There are other reasons why it might skip MEMORY.)

Note: I believe the above comment applies even for TINYTEXT, which is nearly identical to VARCHAR(255).

Prefix indexes are almost always bad for performance. And they have a gotcha -- In your example (with "PRIMARY KEY (a(255))"), you are enforcing that the first 255 bytes of 'a' are distinct. (Yes, that was quite ok for your test, but is usually 'wrong' in real life.)

VARCHAR(255) can hold 255 characters.
TINYTEXT can hold 255 bytes.
Note the subtle difference, especially when running with utf8. This means that the former may hold more stuff.