8

Which type of id is better for querying and to use as foreign key in mysql:

1)Varchar id with size 20

2)Int id

3 Answers3

12

An int is usually better, because it gives better performance.

If your item has a natural key which is a varchar, you could use that and it will work. But you will get a performance improvement (and some other benefits) by adding a surrogate key that is an integer. You can add an AUTO_INCREMENT column for this purpose.

Mark Byers
  • 811,555
  • 193
  • 1,581
  • 1,452
1

Int IDS are more efficient because the database will only have to compare once.

So lets say you have n rows. If you use an int then you're looking at O(n) as a worst case. Where as if you use a varchar(20) you're looking at O(n*20)

poy
  • 10,063
  • 9
  • 49
  • 74
  • 10
    `O(n*20)` is exactly the same as `O(n)`. You can't use big-O notation to express a constant factor difference. – Mark Byers May 04 '12 at 10:59
  • @MarkByers It all depends on the definition and the common understanding. According to wikipedia and what i've learned in universit what you are describing is a simplification of the term which is permitted in Big-O notation. But it is also no problem to put the constant factor in, because here we are in the context of a practical application (an implementation) and we only have a linear term (x instead of x^2 or x^3) and so a factor of 20 or not twenty is extremely relevant. So i'd say here it is beneficial and relevant to the understanding to point out the difference. – Toastgeraet Apr 27 '23 at 12:46
0

INT IDs are always preferable..

It is faster and uses less storage on the database.

There are cases when VARCHAR IDs are needed, but INT is more efficient

Schalk
  • 144
  • 5