Sharding Keys

I've been going over the problem of keys for sharding all day.

I've come full circle and realised auto-increment works perfectly fine.

The trick is that as everything is sharded on user, as long as the userID is present with the items auto-increment, it will work without an issue.

Lets take Jonno prediction 500

Jonno@prediction@500 - seems ok to me

Wherever jonno goes in terms of db shards, his 500th prediction will follow.

This essentially boils down to: find where jonno is, then take prediction 500 from there.

If on the other hand you give each prediction a unique ID, and then try to find it, you'd need to look it up or look in all shards.

This ID will also change anytime more shards are made, thus all urls would break.

In the shard itself using Jonno as the key everywhere isn't so great. Instead its translated to an int, and then works as per usual inside the shard. All cross shard queries would revert to the char username.