What is the advantage of setting a database column VARCHAR(32)
over VARCHAR(255)
even though the two of them consume 2-byte memory?
1 Answer
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.