What is the advantage of setting a character column as VARCHAR(32) over VARCHAR(255)

Question

What is the advantage of setting a database column VARCHAR(32) over VARCHAR(255) even though the two of them consume 2-byte memory?

Answer 1

You are probably defaulted to CHARACTER SET utf8 (or utf8mb4), in which case (255) would need 2 bytes for the length.

But the real problem is with temp tables (pre-8.0): If the SELECT is so complex that it needs to generate a temp table (eg, for sorting), it will try first to use a MEMORY table. In doing so, it will turn VARCHARs into CHARs, thereby taking 765 bytes (if utf8). If, in doing so, this (and other actions) take too much space, it will switch to the slower MyISAM for the temp table. See the status value Created_tmp_disk_tables.

For SSN, country_code, zip, etc, explicitly say CHARACTER SET ascii (or latin1). This will avoid extra bloat for the charset.

For fixed-length columns, use CHAR, not VARCHAR. So, if this is a US SSN, shouldn't it be

SSN CHAR(11) CHARACTER SET ascii  COMMENT 'example: "123-45-6789"'

In spite of all that, the performance difference will be minor.

More important in the serious legal implications (in the US) if your computer is hacked into, and you lose sensitive info such as SSNs. The cost of such a breach could bankrupt your fledgling business. And no, simply encryption won't suffice -- think about how easy the hacker can find the encryption key.