CODEWITHSUNDEEP
mysqlindexinguuid
Josh Mountain
Josh Mountain

Reputation: 1950

Indexing a column with different formats of UUID (V4 and V7)

From my understanding of UUID v7 they are binary sortable which in some way improves indexability. My database is currently storing v4 UUIDs in an indexed column, if I start storing new rows with UUID v7 will I still gain the indexing benefits even if the column has other non sortable rows? What is the best data type to use for this row, currently it is a char(36)

Upvotes: 1

Views: 5498

Answers (1)

Rick James
Rick James

Reputation: 142356

By converting to BINARY(16), it becomes smaller and readily indexed.

V7 is naturally ordered chronologically. V1's bits can be shuffled See 8.0's functions, 10.7's datatype and UUIDs .

None of the other versions have the "chronological" characteristic.

Since the only way to lookup UUIDs is one row at a time, your comments about 'indexability' don't make sense. Perhaps you were referring to "locality of reference" wherein all the "recent" rews will be "next" to each other.

Upvotes: 3

Related Questions