Bad storage method for UUID
Closed this issue · 4 comments
The problem is that LuckPerms stores UUIDs in MySQL in an extremely non-optimized way. It uses VARCHAR(36) for UUIDs but UUID is not a string! It is a 16-byte number!
Not only it increases the amount of excessive data stored but it also makes the SELECT queries way slower (string binary search is way more complicated than simple binary search on numbers).
So I see it is necessary to use BINARY(16) instead of VARCHAR(36).
@lucko What do you think about changing the storage method to a more appropriate one? Is there any other reason to store them as VARCHARs I haven't thought of?
Reference: https://mysqlserverteam.com/storing-uuid-values-in-mysql-tables/
It's indexed. On terms of being much slower, I doubt you'd actually notice a difference in performance on an actual running server. 16 bytes as opposed to 32 isn't that big of difference either in terms of storage performance. Micro optimization at best that would require thousands of tables existing on current servers to be altered
@bob7l For huge networks that use LP with thousands of UUIDs the difference between 32 bytes with string collation and 16 raw bytes can save some storage. But it is not the problem. The problem is with the indexing. Indexing strings is way more complex than indexing raw binary data.
I agree with you that this migration may be a hard process but why can't LP support both schemas? It will allow new users to use an optimized schema while old users won't be required to alter the tables for at least several versions of LP.
Yes, this is something that will be considered if the schema is ever overhauled.
However, doing so is a complex process, and not something I want to rush into.
In the mean time, storing UUIDs in string form isn't the worst thing. I agree it probably is a micro optimisation at best.
Even for huge networks, this is a tiny thing to worry about.
Supporting two schemas isn't that practical.