CODEWITHSUNDEEP
mysqlsqlindexingcreate-table
Alexander
Alexander

Reputation: 5861

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?

Upvotes: 7

Views: 5874

Answers (5)

Scarface97
Scarface97

Reputation: 103

The main thing to consider is whether this field will often be used for searching and filtering through records. If yes, index it. Other answers addressed that well.

However, keep in mind that indexes add some overhead to insert and update operations. If a field is updated constantly, but rarely used in search clauses, you may be disappointed to find out that the index is actually slowing you down instead of improving performance.

Upvotes: 1

KM.
KM.

Reputation: 103627

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

Upvotes: 7

Andy Lester
Andy Lester

Reputation: 93715

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.

Upvotes: 6

Joe Stefanelli
Joe Stefanelli

Reputation: 135848

What fields will you be searching on? Those should be your first candidates for indexes. Also index any columns that will become foreign keys in other tables (userid seems like a likely candidate).

Upvotes: 3

andrewk
andrewk

Reputation: 3871

Primay ID shoud go to the userid. It's very helpful in now and in the long run.

Upvotes: 0

Related Questions