Does ULID Satisfy MySQL Clustered Index Design for Primary Keys?

Question

I'm considering using ULIDs (Universally Unique Lexicographically Sortable Identifiers) as primary keys in MySQL tables, and I'm wondering if ULIDs satisfy MySQL's clustered index design. I've read that clustered indexes in MySQL determine the physical ordering of rows in a table based on the indexed column(s). However, I'm unsure if ULIDs, being lexicographically sortable, are suitable for clustered indexes in MySQL.

Here are my specific questions:

Can ULIDs effectively serve as primary keys in MySQL tables with clustered indexes?

Will MySQL efficiently organize the data based on the lexicographical ordering of ULIDs?

Are there any potential performance or scalability concerns when using ULIDs as primary keys with clustered indexes in MySQL?

Answer 1

No problem, but almost no need.

• It could be CHAR(26) CHARACTER SET ascii (26 bytes) or whatever size the BINARY equivalent is.
• Yes, PRIMARY KEYs can have the above datatypes.
• Since a PK is implicitly added to each secondary key, the size matters some.

The UUIDs available now (both MySQL 8 and MariaDB) have a way to convert to a chronologically-ordered BINARY(16) (16 bytes).