What should I index? Usernames or user identifiers?

Question

I have a pretty simple table (forgive / stupid, I'm still involved. Written for MySQL):

CREATE TABLE IF NOT EXISTS `userdata` ( `userid` UNSIGNED int(18446744073709551615) AUTO_INCREMENT, `username` char(255) NOT NULL, `password` char(255) NOT NULL, `salt` char(255) NOT NULL, `email` char(255) NOT NULL, PRIMARY KEY(`userid`) ); 

I read that adding an index improves query performance since it does not need to browse the entire database. Instead, it will look at the index and collation data (correct me if I am wrong).

I figured out how to create an index well enough, but not what I should index.
Should I have my own index for usernames? Email addresses, user ID or any field that I have not yet added?

Answer 1

you should ONLY create an index based on the actual use of the column

WHERE :
if you have never had WHERE username='xyz' , no index is needed. if you have a lot of them, add an index

in JOIN :
if you never had JOIN xxxx ON x.userid=u.userid , then the index is not needed. if you have a lot of them, add an index

Answer 2

You should have a pointer to almost any column on which you are performing a keyword search. Something will do where userid = ? in one of your queries? Then index on userid . Are you going to search on username ? Then enter the username. What about password ? Probably not, so don't worry.

Answer 3

What fields will you search in? These should be your first index candidates. Also index any columns that will become foreign keys in other tables ( userid seems a likely candidate).

Answer 4

Shoud priority id go to user id. It is very useful now and in the long run.