1

I know this was discussed many times, I am hoping to get an advice for my specific case.

I am working on a social networking site, that in theory should be able to have billions of photos recorded in mysql database.

I was wondering if is a good idea to replace the unique BIGINT id of the records with a string id like 'oij43oj54oi52j43i'.

The advantages would be that:

  1. I could insert photo records into multiple databases spread on multiple servers unlike with the BIGINT id where you need to wait and get the next row number from mysql when inserting.

  2. It would be less likely to allow users to randomly access images by entering integer numbers like /avatars/120.jpg if if it would be /avatars/oij43oj54oi52j43i.jpg. I know this second part can be done by URL rewrite too but would use some CPU/mem.

The other tables like "photo_comments" could also use same string id when referring to the photo, e.g photo_comment_photo_id='oij43oj54oi52j43i';

With these in mind, is it a bad idea to use the string ID? Especially for the first advantage that I mentioned, is that a valid point/advantage/practice ?

Thank you.

Edit: ensuring the string is unique can be done by appending the user id to the string, and maybe use php uniqid() that uses time-stamp microseconds.

adrianTNT
  • 3,671
  • 5
  • 29
  • 35
  • How are you going to ensure oij43oj54oi52j43i is a unique value? Auto increment values were designed for a reason. Don't try to mimic something that already works perfectly, and stick to using auto increment. – Jocelyn Jul 30 '12 at 16:26
  • have you considered guids? see http://stackoverflow.com/questions/547118/storing-mysql-guid-uuids and http://stackoverflow.com/questions/8180019/what-is-the-replacement-for-uniqueidentifier-in-mysql – hatchet - done with SOverflow Jul 30 '12 at 16:27
  • @Jocelyn uniqueness can be achieved by appending the photo user id to php uniqid(), I edited question to include this. For user 25 a photo id can look like 25oij43oj54oi52j43i – adrianTNT Jul 30 '12 at 16:36
  • 1
    Remember that the primary key is used by all secondary keys, so keep your primary key as small as possible. A 64-bit int is highly preferable to a 255 character string. – tadman Jul 30 '12 at 19:46

1 Answers1

3

If you're building something like this you should take a page from Instagram and use a UUID instead of a sequential ID. These have many advantages, but the biggest one is that they are scalable and shardable.

There's a simple UUID library in most languages, and even MySQL has a UUID generator that can be used as a last resort.

Instagram's version isn't a pure UUID but a 64-bit version of something similar enough.

Basically you should read this article on Sharding and IDs at Instagram for inspiration.

tadman
  • 208,517
  • 23
  • 234
  • 262