What should I be indexing? Usernames or User IDs?

Question

I have a fairly simple table (forgive errors / stupidity, I'm still learning. 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've read that adding an index improves the performance of a query, as it doesn't need to look through the entire database. Instead, it will look through the index and match data (correct me if I'm wrong).

I've found out how to create an index well enough, but not what I should be indexing.
Should I have my index on usernames? Email addresses, user ID, or some field I've yet to add?

Answer 1

You should have an index on pretty much any column that you're doing keyed lookups on. Is something going to do a where userid = ? in one of your queries? Then index on userid. Are you going to be doing lookups on username? Then index on username. What about on password? Probably not, so don't bother.

Answer 2

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

usage in a WHERE:
if you never have WHERE username='xyz', the no index is needed
if you have many of these, then add an index

usage in a JOIN:
if you never have any JOIN xxxx ON x.userid=u.userid, then no index is needed
if you have many of these, then add an index