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)

Barbed

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.

Suellensuelo

Thanks, I was pretty sure I wouldn't be a huge difference, but wanted to hear from the community who has "been there done that" – Barbed

Note: for columns which are an ASCII-limited code only rather than real words (eg hashes, base64, standard country codes, etc), it may be a good idea to use the ascii_bin collation. If you use a utf-8 based collation it will reserve 3 or 4 bytes per character for CHAR columns instead of only 1. – Suellensuelo

@displayname what makes you say that? – Suellensuelo

Although the chance is very small, it could change. Why add something to your pk that basically depends on political decisions? ^^ – Lougheed

Fair point though I personally would be willing to accept the work required to adjust things if the country code changed. – Suellensuelo

There is no problem in using a country code as primary key. Yes, country codes changes. So what? The database is not read-only. And use type CHAR(2) CHARSET ascii COLLATE ascii_bin. – Pluck

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.

Pluck

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.

Ebonee

Please link db reference to support it – Constipate