MySQL Design - What is best primary key for our needs? The site I am designing for is in the final stages of development. It is a forum where, obviously, users ask and answer questions.

The owners want id values to be of no value to competitors. These would be the id fields such as question_id, user_id, answer_id, and a couple of others. In other words, they dont want the value of ids in the site to be able to be used by a competitor to determine how many users, or questions, or answers exist. This would also include a private message section since you could sign up, message me, see message_id=3189 in the url or in the html in case of post, and start to piece together how active the site is.

Ok. So, the expectation is for ids to get into the millions (whether it will or not, we are planning for 5 million for each id)

Currently, we are using auto-increment for each table (type is INT unsigned)(ex.questions table has question_id as auto, answers table has answer_id as autoincrement, etc.) and of course there are many joins, and all works well. Even when I insert millions of test records. But, to be honest, I am skeptical of this testing--just adding records--since I am unsure of if there is an impact with multiple users.

We are considering three options to convert the ids so that we make them not usefull to the outside world. And I would love some thoughts from you guys.

1) Add a 4char hash column next to each id column, and index it. Show this hash in the urls. Then whenever a something needs to be processed, lookup the hashes, gete the autoid, and use that in joins and inserts. The con here is the added code and added db calls--sometimes will need two or three hashes to be resolved. Not sure of performance with the lookups.

2) Add the 4char hash column IN PLACE OF the autoid column. And in joining tables make, obviously, the same change. So, the 4char is the primary key. The pro here is that there is very little change to code, just change my db column types. But not sure of performance with the joins or lookups.

3) Generate random numbers for the id field instead of autoincrementing. This gives billions of random possibiilities which would be of no value to others. I would probably opt for only using numbers above 1 billion so all ids are the same length. The pro here is that it is the easiest, but not sure of lookups and joins with fragmented ids.

Well, what do you guys think? Do any of these make more sense than the others? Is this a case of "I'd never notice a difference anyway, even with millions of records?"
"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.