Is there a list of already set standards for some common fields like: firstName, lastName, email, phone?
I see most people just set varchar(300) to all fields, does it affect performance in any way?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
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.