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

Question

Hi, 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

changing that won't have a big effect on performance (depending on how much rows are in that table - probably you won't notice any effect), but maybe it will make your database using less disk space. (i use a lengh of 30 for user names, 64 for passwords(legth of the hash) and 50 for email adresses).

Answer 2

'password' should be varchar(40) if you use SHA1 hashes. You do not need utf8 in there at all since you should store passwords as hashes.

'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.

Regarding performance, I don't think it makes a big difference. String comparisons are always slow. But if you just select but don't order/filter by those fields (at least not without indexes) the max length doesn't make a big difference.

Answer 3

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.)