Any point in indexing a unique field?

poring

Hi,

I have one pretty big table, which contains informations about my users, and I'm trying to optimise it.

My main question is: Is there any point in creating an index on the "username" field, considering these values will be unique?

If not, I thought I could add a field which would only contain the first character of the username, and create an index on this one. I would then do "WHERE first_char = '$first_char' AND username = '$username'"

Do you think this would be a good idea?

LordShryku

If it's a going to be unique, you should put a unique constraint on there or make it the primary key, which would enforce that it's unique and index it at the same time.

poring

ok thanks. But what do you think about my idea of indexing only the first character? See, it would eliminate 25/26 of the rows to search through, right? I thought it would help a lot if the username started with a 'z'. Or am I just being stupid?

LordShryku

It serves no real purpose. If the username is indexed, then you can do a substring search on there and get the same results. Indexing is really only useful when you're querying for 4% or less of the table. If you have a small table, then it's not going to do a lot of good, but it will enforce your constraints and may save you a few milliseconds here and there.