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

Question

Most of the times I define varchar(255) length auto.

But now I thinking how much varchar length should be best to define for utf8 fields:

1. password

2. username

3. email

If this fields should be define less than varchar 255, how much performance it will improve?

Thanks

Answer 1

'password' should be char(40) if you use SHA1 hashes. This might have binary collation if you are sure that the cases of the hash is always the same. This gives you better performance. If you're not, use latin1, but don't use utf8.

'email'... use 255, you cannot know how long someone's email address is.

For the username I'd just use whatever your max username length is. 20 or 30 would probably be good.

If you have an index on a character field (especially if it's part f the PK) choose the length very carefully, because longer and longer indexes might reduce performance heavily (and increases memory usage).

Also, if you use UTF8 char field in an index, you have to be aware, that MySQL reserves 3 times more bytes that the actual character length of the field, preparing for the worst case (UTF8 might store certain characters on 3 bytes). This can also cause lack of memory.

Answer 2

If you index any of those fields (and you don't use a prefix as the index), bear in mind that MySQL will index the field as though it were CHAR rather than VARCHAR, and each index record will use the maximum potential space (so 3n bytes for a VARCHAR(n), since a UTF8 character can be up to 3 bytes long). That could mean the index will be larger than necessary. To get around this, make the field smaller, or index on a prefix.

(I should say: I'm sure I've read that this is the case somewhere in the MySQL documentation, but I couldn't find it when I looked just now.)