From 7df09e31fa2700454beecbaf3c0721e13d6086f4 Mon Sep 17 00:00:00 2001 From: Lunny Xiao Date: Mon, 17 Feb 2025 11:28:37 -0800 Subject: Move issue pin to an standalone table for querying performance (#33452) Noticed a SQL in gitea.com has a bigger load. It seems both `is_pull` and `pin_order` are not indexed columns in the database. ```SQL SELECT `id`, `repo_id`, `index`, `poster_id`, `original_author`, `original_author_id`, `name`, `content`, `content_version`, `milestone_id`, `priority`, `is_closed`, `is_pull`, `num_comments`, `ref`, `pin_order`, `deadline_unix`, `created_unix`, `updated_unix`, `closed_unix`, `is_locked`, `time_estimate` FROM `issue` WHERE (repo_id =?) AND (is_pull = 0) AND (pin_order > 0) ORDER BY pin_order ``` I came across a comment https://github.com/go-gitea/gitea/pull/24406#issuecomment-1527747296 from @delvh , which presents a more reasonable approach. Based on this, this PR will migrate all issue and pull request pin data from the `issue` table to the `issue_pin` table. This change benefits larger Gitea instances by improving scalability and performance. --------- Co-authored-by: wxiaoguang --- services/convert/issue.go | 7 ++++++- services/convert/pull.go | 7 +++++-- services/issue/issue.go | 8 +------- services/repository/delete.go | 1 + 4 files changed, 13 insertions(+), 10 deletions(-) (limited to 'services') diff --git a/services/convert/issue.go b/services/convert/issue.go index 62d0a3b3e6..7f386e6293 100644 --- a/services/convert/issue.go +++ b/services/convert/issue.go @@ -41,6 +41,9 @@ func toIssue(ctx context.Context, doer *user_model.User, issue *issues_model.Iss if err := issue.LoadAttachments(ctx); err != nil { return &api.Issue{} } + if err := issue.LoadPinOrder(ctx); err != nil { + return &api.Issue{} + } apiIssue := &api.Issue{ ID: issue.ID, @@ -55,7 +58,7 @@ func toIssue(ctx context.Context, doer *user_model.User, issue *issues_model.Iss Comments: issue.NumComments, Created: issue.CreatedUnix.AsTime(), Updated: issue.UpdatedUnix.AsTime(), - PinOrder: issue.PinOrder, + PinOrder: util.Iif(issue.PinOrder == -1, 0, issue.PinOrder), // -1 means loaded with no pin order } if issue.Repo != nil { @@ -122,6 +125,7 @@ func toIssue(ctx context.Context, doer *user_model.User, issue *issues_model.Iss // ToIssueList converts an IssueList to API format func ToIssueList(ctx context.Context, doer *user_model.User, il issues_model.IssueList) []*api.Issue { result := make([]*api.Issue, len(il)) + _ = il.LoadPinOrder(ctx) for i := range il { result[i] = ToIssue(ctx, doer, il[i]) } @@ -131,6 +135,7 @@ func ToIssueList(ctx context.Context, doer *user_model.User, il issues_model.Iss // ToAPIIssueList converts an IssueList to API format func ToAPIIssueList(ctx context.Context, doer *user_model.User, il issues_model.IssueList) []*api.Issue { result := make([]*api.Issue, len(il)) + _ = il.LoadPinOrder(ctx) for i := range il { result[i] = ToAPIIssue(ctx, doer, il[i]) } diff --git a/services/convert/pull.go b/services/convert/pull.go index 209d2bd79d..ad4f08fa91 100644 --- a/services/convert/pull.go +++ b/services/convert/pull.go @@ -93,7 +93,7 @@ func ToAPIPullRequest(ctx context.Context, pr *issues_model.PullRequest, doer *u Deadline: apiIssue.Deadline, Created: pr.Issue.CreatedUnix.AsTimePtr(), Updated: pr.Issue.UpdatedUnix.AsTimePtr(), - PinOrder: apiIssue.PinOrder, + PinOrder: util.Iif(apiIssue.PinOrder == -1, 0, apiIssue.PinOrder), // output "[]" rather than null to align to github outputs RequestedReviewers: []*api.User{}, @@ -304,6 +304,9 @@ func ToAPIPullRequests(ctx context.Context, baseRepo *repo_model.Repository, prs if err := issueList.LoadAssignees(ctx); err != nil { return nil, err } + if err = issueList.LoadPinOrder(ctx); err != nil { + return nil, err + } reviews, err := prs.LoadReviews(ctx) if err != nil { @@ -368,7 +371,7 @@ func ToAPIPullRequests(ctx context.Context, baseRepo *repo_model.Repository, prs Deadline: apiIssue.Deadline, Created: pr.Issue.CreatedUnix.AsTimePtr(), Updated: pr.Issue.UpdatedUnix.AsTimePtr(), - PinOrder: apiIssue.PinOrder, + PinOrder: util.Iif(apiIssue.PinOrder == -1, 0, apiIssue.PinOrder), AllowMaintainerEdit: pr.AllowMaintainerEdit, diff --git a/services/issue/issue.go b/services/issue/issue.go index 091b7c02d7..586b6031c8 100644 --- a/services/issue/issue.go +++ b/services/issue/issue.go @@ -197,13 +197,6 @@ func DeleteIssue(ctx context.Context, doer *user_model.User, gitRepo *git.Reposi } } - // If the Issue is pinned, we should unpin it before deletion to avoid problems with other pinned Issues - if issue.IsPinned() { - if err := issue.Unpin(ctx, doer); err != nil { - return err - } - } - notify_service.DeleteIssue(ctx, doer, issue) return nil @@ -319,6 +312,7 @@ func deleteIssue(ctx context.Context, issue *issues_model.Issue) error { &issues_model.Comment{RefIssueID: issue.ID}, &issues_model.IssueDependency{DependencyID: issue.ID}, &issues_model.Comment{DependentIssueID: issue.ID}, + &issues_model.IssuePin{IssueID: issue.ID}, ); err != nil { return err } diff --git a/services/repository/delete.go b/services/repository/delete.go index 2166b4dd5c..fb3fffdca7 100644 --- a/services/repository/delete.go +++ b/services/repository/delete.go @@ -158,6 +158,7 @@ func DeleteRepositoryDirectly(ctx context.Context, doer *user_model.User, repoID &actions_model.ActionSchedule{RepoID: repoID}, &actions_model.ActionArtifact{RepoID: repoID}, &actions_model.ActionRunnerToken{RepoID: repoID}, + &issues_model.IssuePin{RepoID: repoID}, ); err != nil { return fmt.Errorf("deleteBeans: %w", err) } -- cgit v1.2.3