Notification page becomes very slow when the table is very large #13661

New Issue

GiteaMirror · 2025-11-02T10:49:37-06:00

GiteaMirror commented

2025-11-02 10:49:37 -06:00

Originally created by @BoYanZh on GitHub (Oct 31, 2024).

Description

I hosted a Gitea instance with 1410973 rows in the notification table. Now Gitea admin needs ~6000ms to open the notification page. The slow SQL log is as follows:

Oct 31 14:50:44 git-server gitea[2883158]: 2024/10/31 14:50:44 models/db/list.go:164:]() [W] [Slow SQL Query] SELECT `id`, `user_id`, `repo_id`, `status`, `source`, `issue_id`, `commit_id`, `comment_id`, `updated_by`, `created_unix`, `updated_unix` FROM `notification` WHERE notification.user_id=? AND notification.status IN (?,?) ORDER BY notification.updated_unix DESC LIMIT 20 [70 1 3] - 5.11404742s

I tried to add an index in MySQL to temporarily fix it, CREATE INDEX idx_notification_user_status_updated ON notification(user_id, status, updated_unix DESC);. The notification page now only needs 6ms to load.

Should I do something other than adding the index manually to the table or can I create a PR to add the index to the code base?

Gitea Version

1.22.0

Can you reproduce the bug on the Gitea demo site?

No

Log Gist

No response

Screenshots

No response

Git Version

No response

Operating System

No response

How are you running Gitea?

Use my own build from https://github.com/BoYanZh/gitea.

Database

MySQL/MariaDB

Originally created by @BoYanZh on GitHub (Oct 31, 2024). ### Description I hosted a Gitea instance with 1410973 rows in the notification table. Now Gitea admin needs ~6000ms to open the notification page. The slow SQL log is as follows: ``` Oct 31 14:50:44 git-server gitea[2883158]: 2024/10/31 14:50:44 models/db/list.go:164:]() [W] [Slow SQL Query] SELECT `id`, `user_id`, `repo_id`, `status`, `source`, `issue_id`, `commit_id`, `comment_id`, `updated_by`, `created_unix`, `updated_unix` FROM `notification` WHERE notification.user_id=? AND notification.status IN (?,?) ORDER BY notification.updated_unix DESC LIMIT 20 [70 1 3] - 5.11404742s ``` I tried to add an index in MySQL to temporarily fix it, `CREATE INDEX idx_notification_user_status_updated ON notification(user_id, status, updated_unix DESC);`. The notification page now only needs 6ms to load. Should I do something other than adding the index manually to the table or can I create a PR to add the index to the code base? ### Gitea Version 1.22.0 ### Can you reproduce the bug on the Gitea demo site? No ### Log Gist _No response_ ### Screenshots _No response_ ### Git Version _No response_ ### Operating System _No response_ ### How are you running Gitea? Use my own build from https://github.com/BoYanZh/gitea. ### Database MySQL/MariaDB

GiteaMirror added the performance/speed type/bug labels 2025-11-02 10:49:37 -06:00

GiteaMirror closed this issue

2025-11-02 10:49:37 -06:00

GiteaMirror commented

2025-11-02 10:49:38 -06:00

@lunny commented on GitHub (Oct 31, 2024):

Please create a PR to add the index

@lunny commented on GitHub (Oct 31, 2024): Please create a PR to add the index

Sign in to join this conversation.

Branches Tags

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: github-starred/gitea#13661