text indexes vs integer indexes in mysql

Question

Hey,

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

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.

Answer 2

Using the SKU number as a primary key has some sense. You will like to index to make searchs by SKU fast and it is the natural index of the information.

However it has some penalties:

• Performance (as Coronatus said)

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