Simple question

dahamsta

Christ, I'm only 31 and I'm forgetting basic maths already...

I want to use an alphanumeric string ([A-Za-z0-9] / 26+26+10=62) as a unique identifier in a database table. I want to cater for the possibility of millions of rows, plus I want to leave plenty of headroom to try and avoid multiple queries when collisions occur. How long should the string be?

adam

Victor

Originally posted by dahamsta
How long should the string be?

Well how long is a string?

Try adding the date into the identifier and you can go to infinity (and BEYOND!).

ecksor

Originally posted by dahamsta
I want to use an alphanumeric string ([A-Za-z0-9] / 26+26+10=62) as a unique identifier in a database table. I want to cater for the possibility of millions of rows, plus I want to leave plenty of headroom to try and avoid multiple queries when collisions occur. How long should the string be?

You're looking for powers of 62, in the same way that our normal number base increases by powers of 10 each time you increment the length of the string that we conventionally use to represent natural numbers.

62^4 =  14,776,336
62^5 = 916,132,832

4 or 5 characters I suppose.

How will collisions occur if it is a unique constraint?

dahamsta

Originally posted by Victor
Try adding the date into the identifier and you can go to infinity (and BEYOND!).

I'm aiming for the shortest string possible, which is why I was looking at using alphanumeric characters. I'd have to use microtime() in this case because of the risk of two inserts in the same second, which is the opposite direction. Thanks anyway. And less of the bad jokes please.

Originally posted by ecksor
You're looking for powers of 62, in the same way that our normal number base increases by powers of 10 each time you increment the length of the string that we conventionally use to represent natural numbers.

62^4 =  14,776,336
62^5 = 916,132,832

4 or 5 characters I suppose.

That's the stuff ecksor, thanks very much! I was thinking along the lines of 62 x 62 x $chars and couldn't figure out why the number looked so small. But for the first example above it would be 62 x 62 x 62 x 62 x $chars, right?

How will collisions occur if it is a unique constraint?

I'll be generating a string for each record and no doubt the occasional duplicate will occur, so if I try an INSERT and get an error back I'll need to try again. And of course that's more likely to happen if the combinations are limited. I reckon 15m combinations will do fine for the moment though.

Thanks again,
adam

dahamsta

Originally posted by dahamsta
But for the first example above it would be 62 x 62 x 62 x 62 x $chars, right?

Gah! Just 62 x 62 x 62 x 62, right?!

ecksor

Yep, 62^4 = 62 * 62 * 62 * 62

dahamsta

Cool. In the end I went with [a-z] ^ 6, because the key will be going in a URL and all lowercase looks neater. 31 million combinations should keep me going till at least the end of the week.

Thanks again,
adam

ecksor

It's a bit more than 308 million according to my calculator.

dahamsta

Missed a digit. Windows calculator.

scojones

Adam using Windows, has the world gone mad? Get back to your "KDE" mista*





I'm only joking ya know