1

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?

0

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.

  • When a complex SELECT needs to create temporary table (for a subquery, UNION, GROUP BY, etc), the preferred choice is to use the MEMORY engine, which puts the data in RAM. But VARCHARs are turned into CHAR in the process. This makes VARCHAR(255) CHARACTER SET utf8mb4 take 1020 bytes. That can lead to needing to spill to disk, which is slower.
  • In certain situations, InnoDB will look at the potential size of the columns in a table and decide that it will be too big, aborting a CREATE TABLE.
| improve this answer | |
  • Is there a list where I can see how much space takes each character set? – Eduardo Jul 25 '16 at 22:33
  • ascii, latin* - 1 byte per character; utf8 - max of 3; utf8mb4 - max of 4. "No one" needs the other character sets. When converting to CHAR, the max is used. (I can't remember where I got the data.) – Rick James Jul 25 '16 at 22:45
  • thanks. i have published another question. maybe you want to give it a look – Eduardo Jul 25 '16 at 22:46
  • Note: English text in a VARCHAR or TEXT that is CHARACTER SET utf8 will take only 1 byte per char; this has examples of how many bites are needed in utf8 for various languages. – Rick James Jul 25 '16 at 22:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.