[Denvercoder9]

This misses the point. The reason not to use UUIDv4 is that having an index on random values is slow(er), because sequential inserts into the underlying B-tree are faster than random inserts. You're hitting the same problem with your `public_id` column, that it's not the primary key doesn't change that.

[sgarland]

For InnoDB-based DBs that are not Aurora, and if the secondary index isn’t UNIQUE, it solves the problem, because secondary non-unique index changes are buffered and written in batches to amortize the random cost. If you’re hashing a guaranteed unique entity, I’d argue you can skip the unique constraint on this index.

For Aurora MySQL, it just makes it worse either way, since there’s no change buffer.

[hnfong]

Ints as pk would be quicker for joins etc though.

[grugdev42]

Exactly