Re: VARCHAR ( n )

On 22.11.2016 13:38, Stefan Ram wrote:
> We can safely assume that no first name will have more than
> 100 characters, but is there any drawback to use an upper
> limit that is much larger? (in MySQL 5.7)

VARCHAR() fields are stored as the actual content and the length. Hence you earn a penalty when you specify a VARCHAR() length > 255 because then 2 bytes are needed to store the actual length. Same goes for the different BLOB and TEXT types which use 1, 2, 3 or 4 bytes for the length.

The manual here:

https://dev.mysql.com/doc/refman/5.7/en/storage-requirements.html

suggests that even a VARCHAR(1000) field uses an 8-bit length specifier as long as the *content* of the field is shorter than 256 bytes. But this might be wrongly documented. I also remember that different storage engines can handle things differently internally. I.e. InnoDB stores CHAR() and VARCHAR() in the same format internally and pads CHAR() fields only when they are read. Also InnoDB uses some extra logic to store long VARCHAR() fields outside the row. By specifying a shorter maximum length, some of that logic can be bypassed which saves some CPU cycles.

In the end I think the drawbacks from specifying a limit higher than [Quoted] necessary are negligible. But then OTOH it is good practice to always use the smallest possible data type and I see no point why to deviate from that.

XL Received on Tue Nov 22 2016 - 14:17:08 CET