Using mysql8's BIN_TO_UUID/UUID_TO_BIN

[Jens Teglhus Møller]

Hi

I'm looking at optimizing storage for a mysql8 database that will hold +100M rows and one thing that comes to mind is storing uuid's as binary(16) instead of char(36).

Mysql8 has BIN_TO_UUID/UUID_TO_BIN that will encode time-based/type1 UUIDs in a way that makes your index sortable, but I have not been able to figure out if there is a way use these functions transparently, so for instance Record classes will get fields with the UUID data type.

My googlings came up empty, I have been through the documentation and looked at converters and bindings but came up empty.

Can someone please hit me with a cluestick? 

Best regards Jens

[Lukas Eder]

Thanks for your message, Jens.

You're interested in this feature here: https://github.com/jOOQ/jOOQ/issues/8842

Unfortunately, we don't have an answer to this topic yet. This is less trivial than it might at first appear. The conversion to the storage version of the bind value (UUID_TO_BIN) could be implemented in a data type binding, but the conversion to the projection value (BIN_TO_UUID) harder, and would probably require a VisitListener. You can probably pull this off today using these two tools in jOOQ. We'll certainly offer this out of the box in the future, but some research is still needed. It's likely much easier to implement using jOOQ 3.16's new, experimental query object model traversal and replacement:

- https://www.jooq.org/doc/latest/manual/sql-building/model-api/model-api-traversal/

- https://www.jooq.org/doc/latest/manual/sql-building/model-api/model-api-replacement/

I hope this helps,

Lukas

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/abce48ac-247c-48bc-a2d0-9d6b8fec1ed5n%40googlegroups.com.

[Jens Teglhus Møller]

Hi Lukas

Thanks for the answer, yes, that is exactly the feature I'm looking for.

I think I will postpone this optimization for now and wait for you to do the hard work ;-).

Best regards Jens

[Lukas Eder]

You can always use a computed column...

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/9309dcce-dd9f-4a4f-a505-89e6c28e883fn%40googlegroups.com.