Welcome Guest, Not a member yet? Register   Sign In
Creating indexes in mysql?
#1

[eluser]kyleect[/eluser]
I'm creating a users table with the following fields:

id
username
password
email
group
profile

'username' and 'email' are set as varchar, 255 length and as unique keys so multiple users can't register the same username or have more than one account with the same email address however I'm getting the following error message:

Code:
#1071 - Specified key was too long; max key length is 1000 bytes

I change username length to 20 and it works. Now, I could set the username to 20 and be fine as 255 was just a number because I would always change it later but I want to know why the error is coming up as sql is a weak link for me.
#2

[eluser]bretticus[/eluser]
If you are using fields as utf8 for example I believe it uses up 3 bytes per character. Thus, you are limited to 333 characters for a key. This may be because you have an older version of MySQL. I see no reason why username and password would need 255 characters each. You need a username length policy (32 chars is common) and if anyone has an email with more than say 50 or 100 characters, they are out of their minds!!! Smile You could set length to 100 an be totally fine.
#3

[eluser]Aea[/eluser]
I would keep those indexes under 16 characters in size, it'll be much faster, heck you could probably get away with half that size if you wanted to.
#4

[eluser]kyleect[/eluser]
[quote author="bretticus" date="1238546492"]If you are using fields as utf8 for example I believe it uses up 3 bytes per character. Thus, you are limited to 333 characters for a key. This may be because you have an older version of MySQL. I see no reason why username and password would need 255 characters each. You need a username length policy (32 chars is common) and if anyone has an email with more than say 50 or 100 characters, they are out of their minds!!! Smile You could set length to 100 an be totally fine.[/quote]

Thanks, that makes sense.

@Aea: 8-16 characters, for an email address?
#5

[eluser]Aea[/eluser]
For your email address index.




Theme © iAndrew 2016 - Forum software by © MyBB