mysqldatabaserestprimary-keyuuid

using auto-incrementing BIGINT IDs internally as efficient MySQL primary keys and unique UUID fields as opaque REST API IDs


What are problems with using auto-incrementing BIGINT IDs internally as efficient MySQL primary keys and unique UUID fields as opaque REST API IDs?


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