0

I'm using MySQL Innodb database to work as a distributed database among many places and also each place will work offline but at the end all the data will be centralized in one server, I want to generate a unique primary key for registration table that will be unique for all clients, first I thought about int auto_increment but then I realized that this will cause conflict between each client as all of them will generate a new auto_increment from their local database and not from server so in this case all will start from 1 and increment for each new register
now I'm thinking of using a CHAR or VARCHAR to put a prefix for each registration in client's local database, or is there another way to do so?

0

If there are no natural way of identifying a registration, you may want to have a look at GUID:

https://en.wikipedia.org/wiki/Globally_unique_identifier

The upside is that there are plenty of libraries dealing with them. The downside is that they occupy space which may lead to higher B-Tree indexes, i.e. additional I/O. More read I/O is also required because GUID's are evenly distributed, when you read a page the likelihood that you get a related row is low which causes additional pages to be read.

Another option is to add another attribute representing the host and use this in combination with your sequence as an identifier.

primary key(host_id, seq_id)

Make sure that no two host uses the same host_id.

Yet a third option is to partition the seq_id for different hosts. I.e. host 1 uses seq_id 1..n, host 2 uses seq_id n+1..2n and so forth. Not an alternative for the heart of fainted ;-)

  • UUID/GUID has a performance problem if there are a huge number of them; will your dataset eventually exceed what can be cached in RAM? – Rick James Aug 31 '16 at 20:07
  • Therefore, it might be better to do the "prefix" technique. – Rick James Aug 31 '16 at 21:23
  • Usually surrogates are "chronological" and access patterns are "recent"; so there is some "locality of reference", thereby decreasing the IOPs. – Rick James Aug 31 '16 at 22:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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