MySQL - using String as Primary Key

Question

I saw a similar post on Stack Overflow already, but wasn't quite satisfied.

Let's say I offer a Web service. http://foo.com/SERVICEID

SERVICEID is a unique String ID used to reference the service (base 64, lower/uppercase + numbers), similar to how URL shortener services generate ID's for a URL.

I understand that there are inherent performance issues with comparing strings versus integers.

But I am curious of how to maximally optimize a primary key of type String.

I am using MySQL, (currently using MyISAM engine, though I admittedly don't understand all the engine differences).

Thanks.

Answer 1

Nothing wrong with a CHAR or VARCHAR as a primary key.

Sure it'll take up a little more space than an INT in many cases, and may have a miniscule, hard-to-even-measure effect on performance, but there are many cases where it is the most logical choice and may even reduce the number of columns you need by avoiding duplication of purpose.

For instance, country codes or state abbreviations already have standardised character codes and are a good time to use a character based primary key rather than make up an arbitrary integer ID for each in addition.