title: When using UUIDs, should I also use AUTO_INCREMENT?
body: <p>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 <code>BINARY(16)</code> primary key.</p>
  
  <p>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 <a href="https://stackoverflow.com/a/10375151/215384">here</a>). The benefit to <code>AUTO_INCREMENT</code> 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.</p>
  
  <p>My question is whether or not it is a better idea to use an <code>AUTO_INCREMENT</code> 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.</p>
  
  <p>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 <code>AUTO_INCREMENTS</code> for these. Are there drawbacks to using a (non-null) unique index, rather than primary key, for these references (and, of course, the <code>JOIN</code>s that will come with them)?</p>
  
  <p>It might also be worth noting that the master (online) database uses MySQL (InnoDB) and the distributed (offline) databases use SQLite.</p>
  
  <p><strong>Edit:</strong></p>
  
  <p>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 <code>AUTO_INCREMENT</code> 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?</p>