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?
10 Answers
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.
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.
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.
Some of you are mistaken thinking that a varchar(200) takes up more table size on disk than a varchar(20). This is not the case. Only when you go beyond 255 chars does mysql use an extra byte to determine the length of the varchar field data.
Performance? No. Disk storage? Yes, but it's cheap and plentiful. Unless your database will grow to terabyte scale you're probably okay.
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.
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.
Related