When using UUIDs, should I also use AUTO_INCREMENT?
We're building a new web app that will have an offline iPad/Android app version on a number of local devices that will involve inserts of new data. As such we require the use of UUIDs to allow for the necessary two-way synchronization with the master database. For this we'll be storing the UUID as a BINARY(16) primary key.
The problem I've learned after researching is that the time required for non-sequential primary key inserts will increase over time and that these inserts will lead to fragmentation (as answered here). The benefit to AUTO_INCREMENT is that new rows will usually just be added to the end of the table and so will not run into the speed problems with UUIDs.
My question is whether or not it is a better idea to use an AUTO_INCREMENT column as the primary key and then have the UUID column as a non-null unique index? Presumably this will have the speed benefits of sequential inserts whilst retaining the necessary UUIDs required for synchronizing distributed databases.
The one issue I can see with this is that the UUID needs to be used as a reference (using foreign key constraints) to other tables (i.e. a list of problems attached to an inspection which in turn is attached to a site, all of which are involved in inserts and so all of which require UUIDs). Semantically, it makes more sense for the primary key to be the reference, but as its a distributed system we can't use AUTO_INCREMENTS for these. Are there drawbacks to using a (non-null) unique index, rather than primary key, for these references (and, of course, the JOINs that will come with them)?
It might also be worth noting that the master (online) database uses MySQL (InnoDB) and the distributed (offline) databases use SQLite.
Edit:
Considering that it is perhaps better to have the UUID as a primary key (as that's semantically what it is), would I gain the benefit of sequential inserts if I set the UUID as a primary key and the AUTO_INCREMENT column as a non-null unique index? Or is it only the primary key that is of relevance when determining where to insert a new row?
Answer
Using autoincrements as primary plus an uuid column is a valid model, but you would still have to struggle with some problems the autoincrements brings, it all depends on how you do the synchros.
Anyway I've been working with uuid's as primary keys (my current database have half a million records) and it's still pretty fast, it only slow downs a bit on the inserts, but unless you have very high volumes of inserts daily it shouldn't scare you.
If you use Sql-Server another solution you could have a look at is the Sequential UUIDs, which have a slightly greater collision chances than normal UUID's, but the absolute collision chances are still pretty low, and as they are partially sequential that covers the problems with the fragmentation.