MySQL - using String as Primary Key

user_403682 0
Viewed 33120

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)

primary-key-design optimization mysql
3 Answers

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.

user_3281150

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.

user_10932280
Related