What are problems with using auto-incrementing BIGINT IDs internally as efficient MySQL primary keys and unique UUID fields as opaque REST API IDs?
UUIDs have these benefits:
IDs have these benefits:
"Natural" Primary keys (a column or combination of columns that is intrinsically unique):
PRIMARY KEY(a_id, b_id), INDEX(b_id, a_id)
is clearly faster and smaller.UUIDs are 36 or 16 bytes; ids are 8 bytes or 4 or smaller. A natural key may take 0 extra bytes (or may not).
To answer your question: "It depends".
The tables I build have PKs:
(PS: I find REST to be clumsy and provide no real benefits, so I avoid it.)
Based on Comment
Probably you what:
So, in the the main table,
CREATE TABLE main (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
uuid BINARY(16) NOT NULL,
....
PRIMARY KEY (id),
UNIQUE(uuid),
...
) ENGINE=InnoDB
When creating a new row, compute a new UUID, strip the dashes and convert FROM_HEX()
.
When sending a message to the user, include uuid
, not id
.
When receiving a reply message, quickly switch to using id
by looking it up via that available index. Perhaps this way:
SELECT id FROM main WHERE uuid = ?