Closed
Description
Description
When loading the dashboad for a logged in user (i.e. GET /) the response time is quite high at about two to four seconds.
2024/12/15 22:49:32 ...eb/routing/logger.go:102:func1() [I] router: completed GET / for 172.13.37.42:0, 200 OK in 2266.2ms @ web/home.go:32(web.Home)
The reason is one query that is not using an index.
# Time: 241216 0:09:48
# User@Host: gitea[gitea] @ localhost [127.0.0.1]
# Thread_id: 40 Schema: giteadb QC_hit: No
# Query_time: 1.632164 Lock_time: 0.000037 Rows_sent: 1 Rows_examined: 29547
# Rows_affected: 0 Bytes_sent: 0
SET timestamp=1734304188;
SELECT count(*) FROM `action` WHERE user_id=1337 AND is_deleted=0;All other queries are very fast and there are no complaints about response times.
After introducing a new index, the problem is resolved.
CREATE INDEX IDX_action_r2_u_d ON action (`user_id`, `is_deleted`);The dashboard loads now within 250 ms.
Gitea Version
1.22.5, 1.22.6
Can you reproduce the bug on the Gitea demo site?
No
Log Gist
No response
Screenshots
No response
Git Version
1.22.6
Operating System
Debian 12
How are you running Gitea?
- Self hosted Gitea instance, 8 GB Memory and NVMe storage
- mariadb Ver 15.1 Distrib 10.11.6-MariaDB
Database
MySQL/MariaDB