UserID vs Username performance in MySQL

Is it better to store an integer UserID or stor开发者_运维问答e Username as the key.

Which is better for performance?

The questions seems more like a discussion because it's really based on application requirements. However, I will give a general answer why UserID is better for performance.

My 3 reasons why UserID is better for performance:

1. Can be auto incremented saving one less trip to database to check if Username is in use.
2. Allows for users to change usernames
3. Unsigned INT data type only requires up to 4 bytes allowing up to 4294967295 unique ids

My 3 reasons not to use Username:

1. Storage, using an UTF-8 charset will require 3 bytes per character or 1 byte per character for something like latin1
2. For maximum lookup speed, you have to use MyISAM table with fixed-width tables. A fixed-width table on MyISAM gives you a 30% increase in lookup speed versus a dynamic table.
3. MyISAM does table level locking which can cause slower performance if table is a heavy write.

As long as both fields are primary keys the performance will be the same.

The performance will be the same but I would use UserID as an auto incremented primary key.

Well it depends on what you are trying to achieve. Ideally most websites have both UserId and Username and both of these are meant to be unique. When you assign UserID, then you can auto increment it and that ensures that each new entry into the table gets a unique userID and it does the job for you. However, I am assuming you are not planning to have users use the userID as a means to access your website coz that would just be bad design.

In the long run, usernames will be more complex than an automatically incremented Id integer. You might not notice much difference in performance of the comparisons MySql makes, but your domain code might benefit from being able to compare ID numbers rather than strings containing usernames.

If you want to be able to fetch data by username with comparable performance, yet not make the username a primary key, take a look at MySQL's indexing

Here is a good article on indexing.