choose Username or ID as primary key?

Question

my sites dont need use ID at all, then I am trying to manage user by username.

Assume my sites is abc.com, then access to user profile by URL: abc.com/user1

So if you want to view all info of that user, you can get by Model User ( Laravel Framework):

$user = User::find(user1);
$email = $user->email;
$sex = user1->sex;

Second method, if use ID as primary key, but my project no need to use id at all.

$id=User::where(username=user1)->id;
$user = $User::find($id);
 $email = $user->email;
 $sex = user1->sex;

As you can see, First method is faster than second, because "Username" field has indexed. Should i use username for primary key in this situation? I use MySQL.

score 0 · Answer 1 · answered Dec 17 '14 at 00:32

If you don't like using primary keys that are auto generated, there is another way.

Instead of indexing the userID (which is large and slow to index, because it is a string) you can instead index the hash value of userID. You can then do:

CREATE TABLE Users (
  ID BIGINT
  , Username VARCHAR(255)
)

And you can find the user with:

$user = User::find(hash(user1));

You just need to pick a good hash function. A great candidate is MurmurHash (google it).

choose Username or ID as primary key?

1 Answers1