10

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

Improve this question
1

2 Answers 2

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

-3

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.

Improve this answer

5 Comments

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 Great comment but how fast is it compared to integer indexes?
Varchar vs INT: The difference is mostly insignificant compared to fetching the row, parsing things, etc., not numeric vs string comparison.
@RickJames WHich things are parsed?
@Alex78191 - A table row is a structure that contains the column values, transaction ids, connections to other blocks of rows, etc, etc. My point is that the overhead for dealing with the row outweighs the minor difference in cost between VARCHAR and INT. ("Parsing" was a poor word choice on my part.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.