Mysql – VARCHAR, InnoDB MySQL, is inline allocated space variable or fixed

innodbMySQLvarchar

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

Best Answer

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.

Related Question