Importance of varchar length in MySQL table

Question

I have a MySQL table where rows are inserted dynamically. Because I can not be certain of the length of strings and do not want them cut off, I make them varchar(200) which is generally much bigger than I need. Is there a big performance hit in giving a varchar field much more length than necessary?

Answer 1

No, in the sense that if the values you're storing in that column are always (say) less than 50 characters, declaring the column as varchar(50) or varchar(200) has the same performance.

Answer 2

VARCHAR is ideal for the situation you describe, because it stands for "variable character" - the limit, based on your example, would be 200 characters but anything less is accepted and won't fill the allotted size of the column.

VARCHAR also take less space - the values are stored as a one-byte or two-byte length prefix plus data. The length prefix indicates the number of bytes in the value. A column uses one length byte if values require no more than 255 bytes, two length bytes if values may require more than 255 bytes.

For more information comparing the MySQL CHAR to VARCHAR datatypes, see this link.

Answer 3

Performance? No. Disk storage? Yes, but it's cheap and plentiful. Unless your database will grow to terabyte scale you're probably okay.

Answer 4

Being varchar, rather than just char, the size is based on an internal field to indicate its actual length and the string itself. So using varchar(200) is not very different to using varchar(150), except that you have the potential to store more.

And you should consider what happens on an update, when a row grows. But if this is rare, then you should be fine.

Answer 5

There's one possible performance impact: in MySQL, temporary tables and MEMORY tables store a VARCHAR column as a fixed-length column, padded out to its maximum length. If you design VARCHAR columns much larger than the greatest size you need, you will consume more memory than you have to. This affects cache efficiency, sorting speed, etc.

Answer 6

There can be performance hits - but usually not on a level that most users would notice.

When the size of each field is known in advance, MySQL knows exactly how many bytes are between each field/row and can page forward without reading all the data. Using variable characters diminshes this ability for optimization.

Does varchar result in performance hit due to data fragmentation?

Even better, char vs varchar.

For most uses, you'll be fine with either - but there is a difference, and for large scale databases, there are reasons why you'd pick one or the other.