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.

update for my purpose the string was actually just a base62 encoded integer, so the primary key was an integer, and since you're not likely to ever exceed bigint's size it just doesn't make too much sense to use anything else (for my particular use case)

Answer 1

There's nothing wrong with using a CHAR or VARCHAR as a primary key.

Sure it'll take up a little more space than an INT in many cases, but there are many cases where it is the most logical choice and may even reduce the number of columns you need, improving efficiency, by avoiding the need to have a separate ID field.

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

Answer 2

If your external ID is base64, your internal ID is a binary string. Use that as the key in your database with type BINARY(n) (if fixed length) or VARBINARY if variable length. The binary version is 3/4 shorter than the base64 one.

And just convert from/to base64 in your service.

Answer 3

Using string as the type of primary column is not a good approach because If our values can not be generated sequentially and with an Incremental pattern, this may cause database fragmentation and decrease the database performance.