workaround for uuid column type

[yaozhiwang]

Hi there,

I am using umami for web analytics, with the database backed by PlanetScale. I am currently encountering a server performance issue, as discussed here. Some individuals have resolved this by altering the id column to a UUID column. Unfortunately, MySQL does not support the UUID type. I am wondering if there are any workarounds for this issue.

Thank you!

[mscoutermarsh]

Hi @yaozhiwang,

I'm curious if the index has become too large to fit into memory on your database.

To figure this out, you could run:

SELECT database_name, table_name, index_name,
ROUND(stat_value * @@innodb_page_size / 1024 / 1024, 2) size_in_mb
FROM mysql.innodb_index_stats
WHERE stat_name = 'size' AND index_name != 'PRIMARY'
ORDER BY size_in_mb DESC;

To find the size of the index being used for that query. If it is too large, then upgrading to a larger database would help performance.

There are options for storing UUIDs in mysql more efficiently. But given that you don't own the application, I don't believe that's a feasible option for you.

Generally we recommend people use auto incrementing big ints for IDs as they are the most space efficient and ordered.

More info here on using UUIDs with MySQL.

[yaozhiwang]

Thank you!
I check one query, the index used is 36.77MB, and the biggest index is ~67MB. I assume they are not large?

[mscoutermarsh]

Those seem pretty small. Seems unlikely that saving space on the UUID would help.

Could you share the slow query from insights? And the schema for the table? Maybe it's missing an index.

[yaozhiwang]

the slowest queries:

website table:

session table:

[mscoutermarsh]

It looks to me like those are doing full table scans.

To know for sure, you can click into each and run an explain. We have a couple videos on understanding explains here: https://planetscale.com/learn/courses/mysql-for-developers/queries/introduction-to-queries

Once you've confirmed it's doing a full table scan, you could add indexes on the columns that are in the where of the query. More info on indexes

[yaozhiwang]

Yes, when I tried to find which index used by a query just now, I used the explain, output as follows:

I think it is using one of the indexes. And I've tried add some indexes as stated in the link of my first commit, which have a little help, but still too slow.

Speak of the videos, I've learned a lot about mysql and database from them. I'm really appreciate for this great work from you guys. Actually this is the main reason I insisted to use planetscale for my project. (In my first link, some guy said just to switch to postgress, but I don't believe the performance difference can be so large!)

Anyway, thank you for your help! I need more time to dive into this.