Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I found a forum using self made/custom engine still in beta version, previously using vBulletin.

Now, their new engine uses very long URL id, 24 char length.

http://example.com/thread/000000000000000013186559/seo-friendly-title

What will happen to database performace when someone create URL id like this?
What the reason to use fixed and very long ID? This is very bad, isn't it?

FYI: this forum has more than 500,000,000 posts

share|improve this question
    
Do you really pronounce URL as "earl"?!? –  ErikE Mar 5 '12 at 19:39

1 Answer 1

But notice: it's not 24 bytes (256-bit characters), it appears to be a number only. And a 24-digit decimal number can be stored in just 10 bytes (12 digits, 999999999999, uses 5 bytes, E8D4A50FFF, so squaring that will use 10). A bigint is 8 bytes, and it can go up to 9.2 * 10^18. For all you know, they actually do use bigints and ignore the first 5 digits of the number. Even an int can store 500 million, but just 9 times that size will exceed the positive range. Perhaps they simply thought big when they started!

Even if they do use 10 byte keys, sure, it's a little unpleasant, but it's really not that big a deal. It all depends on the structure of the tables being used and their indexes, plus access patterns to those tables.

Anyone who has a database with five hundred million posts has some serious hardware and some developers/DBAs who are paid to keep it in good order.

Perhaps the post ID is sparse and can be converted by a lookup into something much shorter, and the database is built around the shorter key. By sparse, I mean that each next post ID is not sequential with the one before it, similar to credit card numbers, where some internal relationship between the digits proves it's valid. It's all pure speculation until we hear facts from someone who knows the architecture.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.