Skip to content

Use join for the deleting issue actions query #26277

Closed
@otbutz

Description

@otbutz

Description

Our current DELETE query for actions is rather inefficient on MariaDB:

DELETE FROM action WHERE comment_id IN (SELECT id FROM comment WHERE issue_id=?)

func DeleteIssueActions(ctx context.Context, repoID, issueID int64) error {
// delete actions assigned to this issue
subQuery := builder.Select("`id`").
From("`comment`").
Where(builder.Eq{"`issue_id`": issueID})
if _, err := db.GetEngine(ctx).In("comment_id", subQuery).Delete(&Action{}); err != nil {
return err
}
_, err := db.GetEngine(ctx).Table("action").Where("repo_id = ?", repoID).
In("op_type", ActionCreateIssue, ActionCreatePullRequest).
Where("content LIKE ?", strconv.FormatInt(issueID, 10)+"|%").
Delete(&Action{})
return err
}

Caused by https://jira.mariadb.org/browse/MDEV-16289

see https://codeberg.org/forgejo/forgejo/issues/1161 and https://codeberg.org/forgejo/forgejo/pulls/1165

Gitea Version

1.20.2

Can you reproduce the bug on the Gitea demo site?

Yes

Log Gist

No response

Screenshots

No response

Git Version

No response

Operating System

No response

How are you running Gitea?

Binary started via systemd behind a caddy reverse proxy.

Database

None

Metadata

Metadata

Assignees

No one assigned

    Labels

    type/bugtype/upstreamThis is an issue in one of Gitea's dependencies and should be reported there

    Type

    No type

    Projects

    No projects

    Milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions