MySQL Design - What is best primary key for our needs?
"Random" keys (UUIDs, MD5s, etc) can cause a performance problem, so let's avoid them as indexes (including PKs) of tables.

What is exposed?  Stuff in the url parameters?  Nothing else?

Well, the exposed "numbers" could be encrypted copies of the AUTO_INCREMENT values that work nicely otherwise.  Pass the encrypted stuff wherever a competitor might see it, then decrypt it when you need the simple id.

That is, I want to solve the problem at a higher level than in the bowels of the schema.

One approach is

AES_ENCRYPT(CONCAT(id, '-my-salt'), 'my-secret-string')


together with a similar AES_DECRYPT.  See MySQL, PHP, and other documentation.  The "salting" is to avoid a certain level of easy decoding.

You should probably do the en/decryption in the client and assume the connection between the client and MySQL is secure.  (After all, 'my-secret-string' is quite visible to a hacker on the client or server or network between them.)

AES strings are, I think, 8-bit values.  It would probably be good to use BASE64-encoding to make them nearly url-friendly.  You probably still need to "urlencode" to feed to urls.

Note also,...


MD5 is not reversable.
Random numbers are too likely to duplicate.
Truncate AES strings can't be reversed; I tried to minimize the length of the full AES string, but they will still be somewhat fatter than the minimum.