I am optimizing some innodb tables in MySQL, so I ran procedure analsye() to see what the recommendations were.
The results recommended tinytext instead of varchar(255) for all the fields that were previously set up as varchar(255)
Is there a performance gain to be had by using tinytext? I am only concerned about speed here, not size.
I would expect varchar to be faster than tinytext, and from my Googling around that seems to be the general consensus. Of course you would have to test your system to be truly certain.
The reason it's faster is because when MySQL does certain kinds of operations (joins, sorts, etc.) it will often create temporary tables. When you have a BLOB type (such as tinytext) in a temporary table the table will be disk based rather than memory based, which of course would have a performance impact.
You may also want to look at using char(255) - while it does use more space, it has been substantially faster (in my experience) to use a field that is a constant size when doing comparisons later. The extra space can be filled with padding if you're looking for just speed, then ignore the whitespace later.
However, note: MySQL will not allow varchar and char types to exist in the same table. Nor does it [usually] allow comparisons between varchar and char. I found this out last year when doing a table implementation for a hobby project.
CHAR/VARCHAR is going to be faster as these columns are stored in the same page as the main row data*, whereas TEXT types are stored off page (I was wrong, see comment by Harrison).
People used to use tinytext a lot because varchar (annoyingly) trimmed trailing whitespace. This behavior was removed in MySQL 5.0.
(* At least for the first 768 bytes, and with built-in InnoDB, not the new InnoDB plugin ).
Don't believe if anyone tells you that TINYTEXT is stored in other way, than VARCHAR.
The actual differences are:
TINYTEXT and other TEXT fields are stored separately from in-memory row inside MySQL heap, whereas VARCHAR() fields add up to 64k limit (so you can have more than 64k in TINYTEXTs, whereas you won't with VARCHAR).
TINYTEXT and other 'blob-like' fields will force SQL layer (MySQL) to use on-disk temporary tables whenever they are used, whereas VARCHAR will be still sorted 'in memory' (though will be converted to CHAR for the full width).
InnoDB internally doesn't really care whether it is tinytext or varchar. It is very easy to verify, create two tables, one with VARCHAR(255), another with TINYINT, and insert a record to both. They both will take single 16k page - whereas if overflow pages are used, TINYTEXT table should show up as taking at least 32k in 'SHOW TABLE STATUS'.
I usually prefer VARCHAR(255) - they don't cause too much of heap fragmentation for single row, and can be treated as single 64k object in memory inside MySQL. On InnoDB size differences are negligible.