What's the impact of changing the length of a varchar in mysql? [duplicate]

Question

For example if I had a code in PHP that updated the length, by alter table change column and setting the new length, to the longest entry there ever was would this affect greatly if I had 10000 records?

Does MySQL reallocate space in memory for each record of the new size even though the old data is shorter?

This question is MySQL specific

Answer 1

The length of a varchar column is fixed to the length you declare when you create the table. The length can be any value from 0 to 255 (before MySQL 5.0.3) and from 0 to 65,535 (in MySQL 5.0.3 and later).

The storage of the varchar is the bytes of data with the addition of one or two bytes to declare the length of the string. If the maximum length is 255 or less then only 1 byte of length will be added.

If you use alter table and change the maximum length then no data storage size will be affected if the maximum length defined is below 255. If you're increasing the maximum length above 255 then it's up to the storage engine if it forces two bytes or not for values below 255, in that case it will increase by 1 byte for each row.

The char type is different to varchar as char always uses the space required, so if you had char(10) and varchar(10) but only stored "hello" in each, char would use all 10 bytes, vharchar would hold 6 bytes (5 for hello and 1 for the length), therefore changing the size of varchar columns won't allocate more storage space like it would if it was a char type.

The real question now is why would you want PHP to manipulate the varchar size? You should specify the size for a reason, if you want a variable length field that can hold a lot of text (more than 65,535 bytes) and also dynamic so it only uses the minimum space required maybe the TEXT types might be better for your situation?