MySql design database - varchar length for utf8 :: fields 1. password 2. username 3.email

Question

In most cases, I determine the length of the varchar file (255).

But now I think how much better varchar length is best defined for utf8 fields:

• password

• Username

• Email

If these fields should be defined less than varchar 255, then how much will performance improve?

thanks

Answer 1

'password' must be char (40) if you use SHA1 hashes. This may have binary sorting if you are sure that hash cases are always the same. This gives you better performance. If you do not, use latin1, but do not use utf8.

'email' ... use 255, you cannot know how long the email address will be displayed.

For a username, I would use only what has a maximum username length. 20 or 30 would probably be good.

If you have an index in the character field (especially if it is part of f PK), the length is very carefully chosen because longer indexes can significantly reduce performance (and increase memory usage).

In addition, if you use the UTF8 char field in the index, you should know that MySQL reserves 3 times as many bytes, that the actual field character length is prepared for the worst case (UTF8 can store certain characters for 3 bytes). It can also cause out of memory.

Answer 2

If you index any of these fields (and you do not use the prefix as an index), keep in mind that MySQL will index this field as if it were a CHAR , not a VARCHAR , and each index entry will use the maximum potential space ( so 3n bytes for VARCHAR(n) , since the UTF8 character can have a length of up to 3 bytes). This may mean that the index will be larger than necessary. To work around this, reduce the field or pointer to the prefix.

(I have to say: I'm sure I read that this is somewhere in the MySQL documentation, but I could not find it when I looked.)

Answer 3

which will not have a big impact on performance (depending on how many rows are in this table - you may not notice any effect), but maybe it will make your database smaller on disk. (I use lengh 30 for usernames, 64 for passwords (legth of hash) and 50 for email addresses).