CODEWITHSUNDEEP
phpmysqlprimary-key
Boopathi N
Boopathi N

Reputation: 406

which one is best for using as a primary key in db?varchar or number.

In my project i am using mysql database. In that i had created the tables which have the fields (email,username,.....etc).

In this table i kept email as a primary key. Is this method is good to way of using primary key or i have to create number as a primary key like user_id (auto increment). Which one is correct way and gives good performance to connect with other table in same database.

Upvotes: 1

Views: 76

Answers (2)

cEz
cEz

Reputation: 5062

When using the InnoDB engine, the size of the primary key (PK) is important.

The size of the field(s) in the PK will affect the size of any secondary indexes, as it will be stored along with the columns of the index. Choosing the wrong data type will lead to bloated indexes and increased disk usage. It will also mean more of the buffer pool will be used per index

If you need to refer to the row in another table then that will mean further increased memory and disk usage as you will need to store the PK in more than one place. The email address is certainly an example that will waste space if used as PK, since a BIGINT would be 8 bytes vs a VARCHAR of a much larger size

Upvotes: 2

Văn Tuấn Phạm
Văn Tuấn Phạm

Reputation: 659

Varchar comparison is slower than int comparison. However, this does not matter if you simply retrieve a user from the database using the e-mail address. It does matter if you have complex queries with multiple joins.

If you store information about users in multiple tables, the foreign keys to the users table will be the e-mail address. That means that you store the e-mail address multiple times.

Upvotes: 1

Related Questions