9

I have always tried to have an integer primary key on a table no matter what. But now I am questioning if this is always necessary.

Let's say I have a product table and each product has a globally unique SKU number - that would be a string of say 8-16 characters. Why not make this the PK? Typically I would make this field a unique index but then have an auto incrementing int field as the PK, as I assumed it would be faster, easier to maintain, and would allow me to do things like get the last 5 records added with ease.

But in terms of optimisation, assuming I'd only ever be matching the full text field and next doing text matching queries (e.g. like %%) can you guys think of any reasons not to use a text based primary key, most likely of type varchar()?

Cheers, imanc

user307927
  • 748
  • 9
  • 22
  • possible duplicate of http://stackoverflow.com/questions/63090/surrogate-vs-natural-business-keys – outis Apr 05 '10 at 12:12

2 Answers2

6

Using the SKU number as a primary key has some sense. You will like to index it to make searches by SKU fast. And SKU is a natural index.

However it has some penalties:

  • Performance (as Coronatus said)

  • Lack of design flexibility. If, for any reason, the SKU stops being globally unique you will be forced to change not only the table structure but also all your queries.

  • Changing the SKU of one item will force you to change all the relationships in the database.

borjab
  • 11,149
  • 6
  • 71
  • 98
-2

Computers are MUCH faster at comparing numbers than strings.

Also, MySQL indexes of strings only contain the first 4 letters by default.

If you have strings blabfoo, blabbar, blabboo, the index will be totally useless because the first 4 characters are equal, so a search for "blabf" will initally match ALL 3 strings, then iterate over the results.

Basically, never use strings for indexes because they are slow and use more space.

Amy B
  • 17,874
  • 12
  • 64
  • 83
  • 7
    This is simply incorrect. By default MySQL will index the entire string (unless it's a text/blob, in which case you must specify the length), and retrieving rows via an indexed string column is very fast. – grahamparks Jun 18 '11 at 23:19
  • @grahamparks Great comment but how fast is it compared to integer indexes? –  Jun 17 '13 at 16:13
  • Varchar vs INT: The difference is mostly insignificant compared to fetching the row, parsing things, etc., _not_ numeric vs string comparison. – Rick James Oct 05 '19 at 23:27