0

I have a users table with the following fields, id, username.The Id is auto-increment with primary key and I am thinking if I should have the username as the Primary key as well.

I have another table, audit_results with username as a foreign key to the users table

What is the deciding factor to determine which of the following relation (There would be millions of users in the users table) would be faster (assuming there will be complicated join queries in future).

  1. audit_results.username (related to user.username) OR
  2. audit_results.id (related to user.id)

Does it make any difference with varchar or int datatype as the primary key?

enter image description here

enter image description here

Michał Turczyn
  • 32,028
  • 14
  • 47
  • 69
user580950
  • 3,558
  • 12
  • 49
  • 94

1 Answers1

1

varchar(400) - up to 400 characters, each character is represented by one byte (8 bits), so we can have up to 3200 bits.

int(11) - according to this, int is always four bytes (32 bits).

When joining, SQL have to compare particular values.

I think it is obvious which would perform faster in general (how many bits there can be to compare).

Michał Turczyn
  • 32,028
  • 14
  • 47
  • 69
  • I don't think it's obvious. The inference is that comparing strings is 100 times slower than comparing ints, which is unlikely to be true. – Strawberry Aug 29 '18 at 12:12
  • @Strawberry No, my goal was to picture that it will be definitely slower on average. For some particular values string comparison would be even faster than integer comparison. I didn't mean that it will be 100 times slower, but it will be slower in general. – Michał Turczyn Aug 29 '18 at 12:14