Suggested max len
First, I will mention some common strings that are always hex, or otherwise limited to ASCII. For these, you should specify
CHARACTER SET ascii (
latin1 is ok) so that it will not waste space:
UUID CHAR(36) CHARACTER SET ascii -- or pack into BINARY(16) country_code CHAR(2) CHARACTER SET ascii ip_address CHAR(39) CHARACTER SET ascii -- or pack into BINARY(16) phone VARCHAR(20) CHARACTER SET ascii -- probably enough to handle extension postal_code VARCHAR(20) CHARACTER SET ascii -- (not 'zip_code') (don't know the max city VARCHAR(100) -- This Russian town needs 91: Poselok Uchebnogo Khozyaystva Srednego Professionalno-Tekhnicheskoye Uchilishche Nomer Odin country VARCHAR(50) -- probably enough name VARCHAR(64) -- probably adequate; more than some government agencies allow
Why not simply 255? There are two reasons to avoid the common practice of using (255) for everything.
SELECTneeds to create temporary table (for a subquery,
GROUP BY, etc), the preferred choice is to use the
MEMORYengine, which puts the data in RAM. But
VARCHARsare turned into
CHARin the process. This makes
VARCHAR(255) CHARACTER SET utf8mb4take 1020 bytes. That can lead to needing to spill to disk, which is slower.
VARCHAR versus TEXT
Usage hints for
VARCHAR, plus some Best Practice:
CHAR-- it is fixed length; each character is the max length of the
CHARACTER SET(eg, 4 bytes/character for utf8mb4).
CHARACTER SET asciiunless you know otherwise.
VARCHAR(n)will truncate at n characters;
TEXTwill truncate at some number of bytes. (But, do you want truncation?)
*TEXTmay slow down complex
SELECTsdue to how temp tables are handled.