Notification page becomes very slow when the table is very large

[BoYanZh]

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

[lunny]

Please create a PR to add the index