Use join for the deleting issue actions query

[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=?)

gitea/models/activities/action.go

Lines 688 to 702 in ab388de

	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

[wxiaoguang]

Thank you for the report.

The referred fix on codeberg seems not good enough (eg: the test doesn't cover other databases).

I proposed a new fix: Bypass MariaDB performance bug of the "IN" sub-query #26279

---

I didn't use "JOIN" because I think it's better to avoid using too many dialects for difference database, while pre-querying the IDs is fast enough.

[lunny]

The main problem is comment_id in action table is not an index column.

[wxiaoguang]

[lunny]

OK. I see, it's MariaDB's problem.