CODEWITHSUNDEEP
mysqloptimizationindexing
user307927
user307927

Reputation: 786

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 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

Upvotes: 9

Views: 7137

Answers (2)

borjab
borjab

Reputation: 11655

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.

Upvotes: 6

Amy B
Amy B

Reputation: 17977

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.

Upvotes: -2

Related Questions