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)?

  • I don't think you can have maximum lookup speed and minimum storage size at the same time. You'll probably have to choose. Apr 15, 2013 at 7:56
  • Yeah, but since there are multiple data types, there are multiple choices. I'm trying to make the best choice :).
    – oxygen
    Apr 15, 2013 at 7:58
  • 1
    Before voting to close, consider leaving a comment. All I see is a question which fits the FAQ.
    – oxygen
    Apr 15, 2013 at 11:40
  • Don't take it personally. Some regulars who haven't read the FAQ just don't like questions that do not feature source code. Apr 15, 2013 at 11:49

2 Answers 2


As the docs say the min size occupied by a varchar is 1B and the max is 256B (before 5.0.3) and 65,536B (after 5.0.3)

But when it comes to look-up performance always searching on a char will be faster then a varchar (it makes sense since you will have fixed size rows then the look-up process will be much faster because it can calculate exactly where in the data the desired value is )


If you have varchar(100) and inserted a string of 10 chars then the space occupied is 11B (varchar uses 1B to store the length of the string max. 255 for ver <= 5.0.3 and 2B for ver > 5.0.3 since max. 65,535).
If you have char(100) and inserted a string of 10 chars then the space occupied is 100B.


It's defined by the length of what you enter with a 1-2 byte prefix,


VARCHAR(4)  Storage Required
''          1 byte
'ab'        3 bytes
'abcd'      5 bytes

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.