text indexes vs integer indexes in mysql

Posted by imanc on Stack Overflow See other posts from Stack Overflow or by imanc
Published on 2010-04-05T11:33:33Z Indexed on 2010/04/05 11:43 UTC
Read the original article Hit count: 487

Filed under:
|
|

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

© Stack Overflow or respective owner

Related posts about mysql

Related posts about index