CODEWITHSUNDEEP
mysqlallocationvarchar
weixin0129
weixin0129

Reputation: 108

what is the harm using varchar(x)?

As what the document says, mysql does not allocate the full space you specify for varchar. It stores the actual length of data. So I guess there might also be side effects out there. Like if the original length of a varchar record is 10, Now I want to update it to 20 which still does not reach the specified limit 50. However, the space allocated previously is not enough now. So my question is how MySQL deal with situation like this? Will it take long to move everything following that record? or does it have to do that? Is there anyone can tell me how it works internally? Thanks in advance!

Upvotes: 1

Views: 116

Answers (1)

tadman
tadman

Reputation: 211610

MySQL deals with it well enough you shouldn't concern yourself with the details. Although having irregular length records is somewhat slower to manage than having fixed length ones, there is a huge performance gain by reading significantly less data if your records are typically smaller than the fixed size.

Remember that CHAR columns are always stored and retrieved at their maximum size. A 255-character field will require transferring 255 bytes even if the average record has only 10-15 bytes worth of data. This is a significant amount of overhead.

Using CHAR presents a whole host of other problems since the field will always be that length, meaning it must be transferred in its entirety and, typically, stripped in the client. This is way more trouble than it's worth.

The only use in the modern era for CHAR columns is for things that will always be defined and will always be a certain size. One example might be 40-character hex-coded hashes used as identifiers, but these are typically rare and in nearly all cases a VARCHAR will work as well.

Upvotes: 1

Related Questions