Primary key in MySQL: INT(n) or UUID as varchar(36)

Question

Does it make sense to use UUID as primary key in MySQL?

What would be pros and cons of using UUID instead of regular INT, beside trouble of hand querying?

Answer 1

From my point of view, using UUID as primary key in MySQL is bad idea, if we speak about large databases (and large amount of inserts).

MySQL always creates Primary Keys as clustered, and there is no option to switch it off.

Taking this in to consideration, when you insert large amounts of records with non-sequential identifiers (UUIDs), database gets fragmented, and each new insert would take more time.

Advice: Use PostgreSQL / MS-SQL / Oracle with GUIDs. For MySQL use ints (bigints).

Answer 2

The major downside of UUIDs is that you have to create them beforehand if you want to refer back to the record for further usage afterwards (ie: adding child records in dependent foreign keyed tables):

INSERT INTO table (uuidfield, someotherfield) VALUES (uuid(), 'test'));

will not let you see what the new UUID value is, and since you're not using a regular auto_incremented primary key, you can't use last_insert_id() to retrieve it. You'd have to do it in a two-step process:

SELECT @newuid := uuid();
INSERT INTO table (uuidfield, someotherfield) VALUES (@newuid, 'test');
INSERT INTO childtable ..... VALUES (@newuid, ....);