Is there any harm choosing a large value for varchar in MySQL?

Question

I'm about to add a new column to my table with 500,000 existing rows. Is there any harm in choosing a large value for the varchar? How exactly are varchars allocated for existing rows? Does it take up a lot of disk space? How about memory effects during run time?

I'm looking for MySQL specific behavior details not general software design advises.

Answer 1

Depends on what you're doing. See the relevant documentation page for some of the details:

http://dev.mysql.com/doc/refman/5.0/en/char.html

The penalty in disk space isn't really any different than what you have for e.g. TEXT types, and from a performance perspective it MAY actually be faster.

The primary problem is the maximum row size. Note that the exact implications of this differ between storage engines. Consult the MySQL docs for your storage engine of choice for maximum row size information.

I should also add that there can be performance benefits to minimizing row size, but it really depends on your workload, indexing, and just how big the rows are, whether or not it will be meaningful for you.

Answer 2

There's no harm in choosing a large value for a varchar field. Only the actual data will be stored, and MySQL doesn't allocate the full specified length for each record. It stores the actual length of the data along with the field, so it doesn't need to store any padding or allocate unused memory.

Answer 3

MySQL VARCHAR fields store the contents, plus 2 bytes for length. So empty VARCHAR fields will use up space to mark their lengths.

Also, if this is the only VARCHAR field in your table, and your storage engine is MyISAM, it would force dynamic row format which may yield a performance hit (testing will confirm).

http://dev.mysql.com/doc/refman/5.0/en/column-count-limit.html

http://dev.mysql.com/doc/refman/5.0/en/dynamic-format.html