Importance of varchar length in MySQL table

Question

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.

Size is performance! The smaller the size, the better. Not today or tomorrow, but some day your tables will be grown to a size when it comes to serious bottlenecks, no matter what design you laid out. But you can foresee some of those potential bottlenecks in your design phase that are likely to happen first and try to expand the time your db will perform fast and happily until you need to rethink your scheme or scale horizontally by adding more servers.

In your case there are many performance leaks you can run into: Big joins are nearly impossible with long varchar columns. Indexing on those columns are a real killer. Your disk has to store the data. One memory page can hold less rows and table scans will be much slower. Also the query cache will be unlikely to help you here.

You have to ask yourself: How many inserts per year may happen? What is the average length? Do I really need more than 200 characters or can I catch that in my application front-end, even by informing users about the maximum length? Can I split up the table into a narrow one for fast indexing and scanning and another one for holding additional, less frequently needed data of expanding size? Can I type the possible varchar data into categories and so extract some of the data into a few smaller, maybe int or bool-type columns and narrow the varchar column that way?

You can do a lot here. It may be best to go with a first assumption and then re-design step by step using real-life measured performance data. Good luck.

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

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.