Random ID generation on Sign Up - Database Performance

Question

Hey!

I am making a site that each account will have an ID. But, I didn't want to make it incrementable, meaning:
id=1
id=2
...
id=1000

What I want is to have random IDs:
id=2355
id=5647734
id=23532
...

(The reason is to avoid robots to check all accounts profiles by just incrementing a ID in URL - and maybe other reason, but that is not the question)
But, I am worried about performance on registration.
It will be something like this:

while (RANDOM_ID is not taken): generate new RANDOM_ID

On generating a new ID for the new account, I will query database (MySQL) to check if the ID exists, for each generation.
Is there any better solution for this?
Is there any disadvantage of using random IDs?

Thanks in advance.

Answer 1

You can use UUIDs. It's a unique identifier generated based partly on timestamp. It's almost certainly guaranteed to be unique so you don't have to do a query to check.

i do not know what language you're using, but there should be library or sample code for this for most languages.

Answer 2

There are many, many reasons not to do this:

• Your solution, as written, is not transactionally-safe; two transactions at the same time could both generate the same "random" ID.

• If you serialize the transaction in order to make it safe, you will slaughter performance because the query will keep every single collision row locked until it finds a spare ID.

• Using a random ID as the primary key will fragment the hell out of your clustered index. This is bad enough with uuids - the whole point of an auto-generated identity column is so you can generate a safe sequence out of it.

Why not use a regular primary key, but just don't use that in any of your URLs? Generate a secondary non-sequential ID along with it - such as a uuid - index it, and use this column in any public-facing segments of your application instead of the primary key if you are really worried about security.

Answer 3

Yes you can use UUID but keep your auto_increment field. Just add a new field and set it so something like: md5(microtime(true).rand()) or whatever other method you like and use that unike key along the site to make the links instead to expose the primary key in urls.