API REST & MySQL : using both auto increment ID and uuid

Question

I am creating a REST API with a MySQL database. I would like to know if using auto-incrementing IDs as primary keys, (to keep good performance) and unique uuid fields (used as API ID) is a bad idea? If so why?

(from Comment) The purpose of the UUID is to provide an opaque id in the API, while using a simpler, more efficient, BIGINT for internal purposes.

Answer 1

UUIDs have these benefits:

• They can be created independently by multiple clients, while being unique.
• They obfuscate the ids. (Example: avoid hackers discovering valid ids.)

IDs have these benefits:

• Smaller disk space (and cache) needed, hence somewhat faster.
• Temporarily oriented ("recent" inserts are clustered "together"). This is a performance benefit for very large tables (or small RAMs).

"Natural" Primary keys (a column or combination of columns that is intrinsically unique):

• may be smaller
• may be faster
• more logical.
• Example: In the case of a many-to-many mapping table (just 2 ids pointing to two other tables), 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:

1. Natural - 2/3 of the tables
2. Auto_inc - 1/3
3. UUID - essentially none.

(PS: I find REST to be clumsy and provide no real benefits, so I avoid it.)

Based on Comment

Probably you what:

• An auto_inc id everywhere in the database;
• A UUID for opaquely sending to the user. This avoids various hacking games that might be played with an auto_inc.

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 = ?