Slow loading of dashboard view

[5pr1nz]

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

[lunny]

Thank you for your report. This has been fixed by #32333. The approach is the same as you. v1.23.0-rc0 will be released very soon. Since it will migrate the database, so that cannot be backported to v1.22.

[GiteaBot]

We close issues that need feedback from the author if there were no new comments for a month. 🍵