CODEWITHSUNDEEP
mysqlguid
Greg Pete
Greg Pete

Reputation: 69

How do I set GUID / UUID as primary (and not clustered) key in MySQL?

Reason I'm using GUID / UUID as primary key: Data syncing across devices. I have a master database on the server, and then each device has its own database with the same structure (although, different engines. MySQL on the server, SQLite on the Android devices, etc).

I've read that if you're going to use GUID's as your primary key, it should at least not be the clustering key. However, I can't find how to do that with MySQL. All I can find is this reference that says if you have a primary key, InnoDB will use it as the clustering key.

Am I missing something?

Upvotes: 2

Views: 674

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562260

The article you linked to is about Microsoft SQL Server, which gives you the option of which index to use as the clustering key.

With MySQL's InnoDB storage engine, you have no option. The primary key is always used as the clustering key. If there is no primary key, then it uses the first non-null unique key. Absent any such unique key, InnoDB generates its own internal 6-byte key as the clustering key.

So you could make a table that uses a GUID as a non-unique key, but in practice use it as a candidate key.

CREATE TABLE MyTable (
  guid CHAR(32) NOT NULL,
  /* other columns... */
  KEY (guid) -- just a regular secondary index, neither primary nor unique
);

However, there's a legitimate use for the clustering key. If you frequently do lookups based on the GUID, they will be more efficient if you use the GUID as the clustering key.

The concerns about using a GUID as the clustering key are mostly about space. Inserting into the middle of a clustered index can cause a bit of fragmentation, but that's not necessarily a huge problem in MySQL.

The other issue is that in InnoDB, secondary indexes implicitly contain the primary key, so a CHAR(32) or whatever you use to store the GUID is going to be appended to each entry in other indexes. This makes them take more space than if you had used an integer as the primary key.

Upvotes: 3

Related Questions