mysqldatabaseapiprimary-keyuuid

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


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.


Solution

  • UUIDs have these benefits:

    IDs have these benefits:

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

    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:

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