Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

migrated from stackoverflow.com Apr 15 '13 at 13:25

This question came from our site for professional and enthusiast programmers.

    
I don't think you can have maximum lookup speed and minimum storage size at the same time. You'll probably have to choose. –  Álvaro G. Vicario Apr 15 '13 at 7:56
    
Yeah, but since there are multiple data types, there are multiple choices. I'm trying to make the best choice :). –  Tiberiu-Ionuț Stan Apr 15 '13 at 7:58
1  
Before voting to close, consider leaving a comment. All I see is a question which fits the FAQ. –  Tiberiu-Ionuț Stan Apr 15 '13 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. –  Álvaro G. Vicario Apr 15 '13 at 11:49

2 Answers 2

up vote 1 down vote accepted

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 )

EDIT

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.

share|improve this answer

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

i.e

VARCHAR(4)  Storage Required
''          1 byte
'ab'        3 bytes
'abcd'      5 bytes
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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