text indexes vs integer indexes in mysql

Question

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

Answer 1

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.