text indexes vs integer indexes in mysql

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 produc开发者_Python百科t 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

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.

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.