Missing combined index on issue_user issue_id and uid

[sebastian-sauer]

Description

There is currently no combined index on issue_user uid and issueid.
For a lot of operations these two params are used in the where query and adding the index brought some noticeable performance benefits.

Gitea Version

1.20.5

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?

gitea executable

Database

MySQL/MariaDB

[JakobDev]

Does XORM support combined Indexes?

[KazzmanK]

From docs https://xorm.io/docs/chapter-02/4.columns/

index/index(indexname)	column is index. if add (indexname), the column is used for combined index with the field that defining same indexname.

[sebastian-sauer]

But I'm not sure how to handle the current case - there is already an index on both fields but no combined.
@lunny how can we solve this? is there a way to add an additional combined index via xorm?

[delvh]

@JakobDev yes, with the same mechanism as for composite unique keys as @KazzmanK already pointed out.
@sebastian-sauer yes, using the mechanism described by @KazzmanK on top.

[sebastian-sauer]

Not sure i understand correctly - can i just use sth like the following code:

type IssueUser struct {
	ID          int64 `xorm:"pk autoincr"`
	UID         int64 `xorm:"INDEX INDEX(uidtoissue)"` // User ID.
	IssueID     int64 `xorm:"INDEX INDEX(uidtoissue)"`
	IsRead      bool
	IsMentioned bool
}

As i want to keep the index only on UID and add a combined index.

[delvh]

Exactly.

[sebastian-sauer]

Great - will create a pr with that.

[KazzmanK]

Great - will create a pr with that.

If you need to keep UID index there is no need to have dedicated one, combined (UID,IssueID) index will support querying by UID alone. Just be sure, that XORM`s combined index will be created as (UID,IssueID), but not (IssueID,UID)

type IssueUser struct {
	ID          int64 `xorm:"pk autoincr"`
	UID         int64 `xorm:"INDEX(uidtoissue)"` // User ID.
	IssueID     int64 `xorm:"INDEX INDEX(uidtoissue)"`
	IsRead      bool
	IsMentioned bool
}

Other question, what if I need to have two combined indexes , (UID,IssueID) and (IssueID,UID), looks like it is not possible.

[lunny]

Currently, xorm doesn't support index sequences. But more than one index is supported.

[wxiaoguang]

Currently, xorm doesn't support index sequences. But more than one index is supported.

It supports.

[lunny]

Currently, xorm doesn't support index sequences. But more than one index is supported.

It supports.

Ah, yes. Just forgot that.