Index page Very Slow SQL Query

[Jimmy2099]

Description

Database is TIDB

3539.2ms +
18.163207741s +
10.757722681s

2024/07/07 08:04:43 ...eb/routing/logger.go:102:func1() [I] router: completed GET /user/events for 192.168.31.36:0, 200 OK in 55129.3ms @ events/events.go:18(events.Events)
2024/07/07 08:04:46 ...eb/routing/logger.go:68:func1() [W] router: slow      GET / for 192.168.31.36:0, elapsed 3539.2ms @ web/home.go:32(web.Home)
2024/07/07 08:05:01 ...activities/action.go:461:GetFeeds() [W] [Slow SQL Query] SELECT `action`.* FROM `action` INNER JOIN `repository` ON `repository`.id = `action`.repo_id WHERE act_user_id IN (SELECT `user`.id FROM `user` WHERE (keep_activity_private=? AND visibility IN (?,?)) OR id=? OR (type=? AND `user`.id IN (SELECT org_id FROM team_user WHERE uid=?))) AND repo_id IN (SELECT id FROM repository WHERE (`repository`.is_private=? AND `repository`.owner_id NOT IN (SELECT id FROM `user` WHERE type=? AND visibility IN (?))) OR `repository`.id IN (SELECT repo_id FROM `access` WHERE `access`.user_id=? AND `access`.mode>?) OR `repository`.id IN (SELECT `team_repo`.repo_id FROM team_repo INNER JOIN team_user ON `team_user`.team_id = `team_repo`.team_id WHERE `team_user`.uid=?) OR `repository`.owner_id=? OR (`repository`.is_private=? AND `repository`.owner_id IN (SELECT `org_user`.org_id FROM org_user WHERE `org_user`.uid=?))) AND user_id=? AND is_deleted=? ORDER BY `action`.`created_unix` DESC LIMIT 20 [false public limited 2 1 2 false 1 private 2 0 2 2 false 2 2 false] - 18.163207741s
2024/07/07 08:05:12 ...activities/action.go:461:GetFeeds() [W] [Slow SQL Query] SELECT count(*) FROM `action` INNER JOIN `repository` ON `repository`.id = `action`.repo_id WHERE act_user_id IN (SELECT `user`.id FROM `user` WHERE (keep_activity_private=? AND visibility IN (?,?)) OR id=? OR (type=? AND `user`.id IN (SELECT org_id FROM team_user WHERE uid=?))) AND repo_id IN (SELECT id FROM repository WHERE (`repository`.is_private=? AND `repository`.owner_id NOT IN (SELECT id FROM `user` WHERE type=? AND visibility IN (?))) OR `repository`.id IN (SELECT repo_id FROM `access` WHERE `access`.user_id=? AND `access`.mode>?) OR `repository`.id IN (SELECT `team_repo`.repo_id FROM team_repo INNER JOIN team_user ON `team_user`.team_id = `team_repo`.team_id WHERE `team_user`.uid=?) OR `repository`.owner_id=? OR (`repository`.is_private=? AND `repository`.owner_id IN (SELECT `org_user`.org_id FROM org_user WHERE `org_user`.uid=?))) AND user_id=? AND is_deleted=? [false public limited 2 1 2 false 1 private 2 0 2 2 false 2 2 false] - 10.757722681s

Gitea Version

gitea/gitea:1.22

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?

docker

Database

TIDB

[somera]

To evaluate it I will take the query an do an explain analyse with an sql client to see what happens there. Perhaps the database should be tuned. Or there is an index missing.

[lunny]

Duplicate of #32112 since more details and discussion there.