Re: PRIMARY KEYs: UUID / GUID vs BIGINT (timestamp+random)

[Crocodillian Mofous]

Thanks for your feedback Rick.

I decided to go with this, including a server_id as you mentioned. I've got 19 digits to play with, so I'm using them like this...

- First 10 digits are the unix timestamp
- Next 4 digits are the server_id
- Last 5 digits are random

So they look like this...

1308197198000163841
ttttttttttssssrrrrr

This keeps things visually clear, I can easily read the server_id when browsing through my DB. And for my purposes 10,000 rows per server per second is heaps, I'm not doing any sort of mass row creation jobs. Also I'm altering my application to handle duplicate errors and try again.

My server_ids are created on the central master server, so I'm still using INT auto_increment for them. 4 digits for server_id is probably overkill, but at least this way I feel comfortable spreading server_id numbers out, for example the servers in my development set up are numbered in the 9xxx range.

My application is basically a system of multiple intranets. Each company's intranet has it's own MySQL database/schema created. They all have the same structure and the table structures are all sync'ed automatically from the master template. Not sure if this was the best idea from the start, might change down the track, but that's a whole separate story really.

Within each intranet, rows are really only created when users click on stuff, so all records each database are probably only going to cover a range of 5 years or so. So there isn't really a huge spread of IDs. So my indexes should be ok I think? My understanding on the internals of indexes is pretty limited, hence starting this thread in the performance section.