Query time is very long

[Hsiao-Feng]

I found that it takes 4s to query a data with 3k views. Compared with the demo on the official website, this speed is very slow. Especially when I query data for the past few months ( 200k views of 90 days ) , it takes a minute to return the results.

So I checked the htop dashboard when querying, and found that the CPU occupied about 30% and the memory occupied about 20% during the query. So I don't think this seems to be a server performance issue.

Is there anything I can do to troubleshoot the cause?

This is my MariaDB version:

mysql  Ver 15.1 Distrib 10.6.14-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

[yuanfanzz]

I have over 100K visits every day, and it takes tens of seconds to open the weekly report (on a 4 cores Ryzen 7950x server). Therefore, I had to stop using Umami because it writes millions of records in the database and does not regularly clean up and summarize them.

[Hsiao-Feng]

I use MySQL. I think it might be better to use PostgreSQL instead, but I haven't tried it.

By the way, I changed the ID column from VARCHAR to UUID based on this comment in the discussion forum, and the speed has improved: #2013 (reply in thread)

[juangacovas]

Hey Hsiao, I wrote this for those of us who manually changed VARCHAR(36) to UUID... in case you need it. Also opened an issue to see if developers want to take care of this some day :)

[Hsiao-Feng]

Hey Hsiao, I wrote this for those of us who manually changed VARCHAR(36) to UUID... in case you need it. Also opened an issue to see if developers want to take care of this some day :)

Thank you for telling me about this possible problem. I may indeed encounter this problem. Thank you for giving me a solution. ☺️