What should I be indexing? Usernames or User IDs?

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

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

Advertisement

Answer