For a log table, I have a custom message column.
- It will never be updated.
- It will be searched with LIKE conditions.
- It will be capped at 1KB.
TEXT fields are slower than VARCHAR fields for lookups because they stored separately, while VARCHAR fields are stored inline.
What I don't know and couldn't find here on SO (there already are hundreds of VARCHAR vs TEXT questions) or on the MySQL docs: when inlined, do VARCHAR fields take at least the number of bytes specified, or do they vary?.
One of the goals is keeping the size on disk as small as possible. If VARCHAR does not vary, then TEXT has a significant advantage. Otherwise, VARCHAR will provide both lookup speed and small disk footprint.
EDIT: For clarification, if the field is created with ASCII collationwith and a size of 100, a string of length 10 will take 10 bytes or 100 bytes on disk (for inline VARCHAR)?