Improve some indexes with mysql/mariadb #12151

New Issue

GiteaMirror · 2025-11-02T10:00:15-06:00

GiteaMirror commented

2025-11-02 10:00:15 -06:00

Originally created by @camlafit on GitHub (Dec 4, 2023).

Description

Hi

I get some timeout when users are connected. After some search I've found some issues about long sql queries.
I've improve general behavior with some new indexes.

Follow long queries found, index creation and explain before/after change.

trouble with mode column

SELECT user_id FROM `access` WHERE (repo_id = 653 AND mode >= 2);

CREATE INDEX access_repo_id_IDX USING BTREE ON access (repo_id,mode);

index	id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
Before	1	SIMPLE	access	ALL					678282	Using where
after	1	SIMPLE	access	range	access_repo_id_IDX	access_repo_id_IDX	14		540	Using index condition

SELECT
	`id`,
	`uuid`,
	`repo_id`,
	`issue_id`,
	`release_id`,
	`uploader_id`,
	`comment_id`,
	`name`,
	`download_count`,
	`size`,
	`created_unix`
FROM
	`attachment`
WHERE
	(comment_id = 14054);

CREATE INDEX attachment_comment_id_IDX USING BTREE ON attachment (comment_id);

index	id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
Before	1	SIMPLE	attachment	ALL					1439	Using where
After	1	SIMPLE	attachment	ref	attachment_comment_id_IDX	attachment_comment_id_IDX	9	const	1

access table

SELECT user_id FROM `access` WHERE (repo_id = 653 AND mode >= 2);

CREATE INDEX access_repo_id_IDX USING BTREE ON access (repo_id,mode);

index	id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
before	1	SIMPLE	access	ALL					678282	Using where
after	1	SIMPLE	access	range	access_repo_id_IDX	access_repo_id_IDX	14		540	Using index condition

Comments

SELECT `id`, `uuid`, `repo_id`, `issue_id`, `release_id`, `uploader_id`, `comment_id`, `name`, `download_count`, `size`, `created_unix` FROM `attachment` WHERE (comment_id=62188);

CREATE INDEX attachment_comment_id_IDX USING BTREE ON attachment (comment_id);

index	id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
before	1	SIMPLE	attachment	ALL					1468	Using where
after	1	SIMPLE	attachment	ref	attachment_comment_id_IDX	attachment_comment_id_IDX	9	const	1

Get team user by repo

SELECT
	DISTINCT `team_user`.uid
FROM
	`team_user`
INNER JOIN `team_repo` ON
	`team_repo`.team_id = `team_user`.team_id
INNER JOIN `team_unit` ON
	`team_unit`.team_id = `team_user`.team_id
WHERE
	(`team_repo`.repo_id = 653
		AND `team_unit`.access_mode >= 2);

CREATE INDEX team_unit_access_mode_IDX USING BTREE ON team_unit (access_mode,team_id);

index	id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
before	1	SIMPLE	team_unit	ALL	UQE_team_unit_s				113	Using where; Using temporary
before	1	SIMPLE	team_repo	eq_ref	UQE_team_repo_S	UQE_team_repo_S	18	team_unit.team_id,const	1	Using index
before	1	SIMPLE	team_user	ref	UQE_team_user_S	UQE_team_user_S	9	team_unit.team_id	82	Using index
after	1	SIMPLE	team_unit	range	UQE_team_unit_s,team_unit_access_mode_IDX	team_unit_access_mode_IDX	5		105	Using where; Using index; Using temporary
after	1	SIMPLE	team_repo	eq_ref	UQE_team_repo_S	UQE_team_repo_S	18	team_unit.team_id,const	1	Using index
after	1	SIMPLE	team_user	ref	UQE_team_user_S	UQE_team_user_S	9	team_unit.team_id	82	Using index

Get contributions by user

SELECT
	created_unix DIV 900 * 900 AS timestamp,
	count(user_id) as contributions
FROM
	`action`
WHERE
	act_user_id IN (
	SELECT
		`user`.id
	FROM
		`user`
	WHERE
		keep_activity_private = 0
		AND visibility = 0)
	AND repo_id IN (
	SELECT
		id
	FROM
		repository
	WHERE
		(`repository`.is_private = 0
			AND `repository`.owner_id NOT IN (
			SELECT
				id
			FROM
				`user`
			WHERE
				type = 1
				AND visibility IN (2, 1))))
	AND user_id = 29
	AND act_user_id = 29
	AND (created_unix > 1671463009)
GROUP BY
	timestamp
ORDER BY
	timestamp;

CREATE INDEX user_type_IDX USING BTREE ON user (type,visibility);

index	id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
before	1	PRIMARY	user	const	PRIMARY	PRIMARY	8	const	1	Using temporary; Using filesort
before	1	PRIMARY	action	ref	IDX_action_au_r_c_u_d,IDX_action_r_u_d	IDX_action_au_r_c_u_d	9	const	88088	Using where; Using index
before	1	PRIMARY	repository	eq_ref	PRIMARY,IDX_repository_is_private	PRIMARY	8	action.repo_id	1	Using where
before	4	MATERIALIZED	user	ALL	PRIMARY				564	Using where
after	1	PRIMARY	user	const	PRIMARY	PRIMARY	8	const	1	Using temporary; Using filesort
after	1	PRIMARY	action	ref	IDX_action_au_r_c_u_d,IDX_action_r_u_d	IDX_action_au_r_c_u_d	9	const	88088	Using where; Using index
after	1	PRIMARY	repository	eq_ref	PRIMARY,IDX_repository_is_private	PRIMARY	8	action.repo_id	1	Using where
after	4	MATERIALIZED	user	range	PRIMARY,user_type_IDX	user_type_IDX	9		2	Using where; Using index

Gitea Version

1.19.2

Can you reproduce the bug on the Gitea demo site?

No

Operating System

Debian 12 / Mariadb 1:10.11.4-1~deb12u1

How are you running Gitea?

build from source as explained

Database

MySQL/MariaDB

Originally created by @camlafit on GitHub (Dec 4, 2023). ### Description Hi I get some timeout when users are connected. After some search I've found some issues about long sql queries. I've improve general behavior with some new indexes. Follow long queries found, index creation and explain before/after change. #### trouble with mode column ``` SELECT user_id FROM `access` WHERE (repo_id = 653 AND mode >= 2); ``` CREATE INDEX access_repo_id_IDX USING BTREE ON access (repo_id,mode); | index |id|select_type|table |type|possible_keys |key |key_len|ref |rows|Extra| | ----------------|--|-----------|----------|---|-------------------------|-------------------------|-------|-----|----|-----| | Before | 1|SIMPLE |access|ALL | | | | |**678282**|Using where| | after | 1|SIMPLE |access|range|access_repo_id_IDX|access_repo_id_IDX|14 | |**540** |Using index condition| #### Trouble attachment related to a comment ``` SELECT `id`, `uuid`, `repo_id`, `issue_id`, `release_id`, `uploader_id`, `comment_id`, `name`, `download_count`, `size`, `created_unix` FROM `attachment` WHERE (comment_id = 14054); ``` CREATE INDEX attachment_comment_id_IDX USING BTREE ON attachment (comment_id); | index |id|select_type|table |type|possible_keys |key |key_len|ref |rows|Extra| | ----------------|--|-----------|----------|---|-------------------------|-------------------------|-------|-----|----|-----| | Before | 1|SIMPLE |attachment|ALL | | | | |**1439**|Using where| | After | 1|SIMPLE |attachment|ref |attachment_comment_id_IDX|attachment_comment_id_IDX|9 |const|**1** | | #### access table ``` SELECT user_id FROM `access` WHERE (repo_id = 653 AND mode >= 2); ``` CREATE INDEX access_repo_id_IDX USING BTREE ON access (repo_id,mode); | index |id|select_type|table |type|possible_keys |key |key_len|ref |rows|Extra| | ----------------|--|-----------|----------|---|-------------------------|-------------------------|-------|-----|----|-----| | before|1|SIMPLE |access|ALL | | | | |**678282**|Using where| | after | 1|SIMPLE |access|range|access_repo_id_IDX|access_repo_id_IDX|14 | |**540** |Using index condition| #### Comments ``` SELECT `id`, `uuid`, `repo_id`, `issue_id`, `release_id`, `uploader_id`, `comment_id`, `name`, `download_count`, `size`, `created_unix` FROM `attachment` WHERE (comment_id=62188); ```` CREATE INDEX attachment_comment_id_IDX USING BTREE ON attachment (comment_id); | index |id|select_type|table |type|possible_keys |key |key_len|ref |rows|Extra| | ----------------|--|-----------|----------|---|-------------------------|-------------------------|-------|-----|----|-----| |before | 1|SIMPLE |attachment|ALL | | | | |**1468**|Using where| |after | 1|SIMPLE |attachment|ref |attachment_comment_id_IDX|attachment_comment_id_IDX|9 |const|**1** | | #### Get team user by repo ``` SELECT DISTINCT `team_user`.uid FROM `team_user` INNER JOIN `team_repo` ON `team_repo`.team_id = `team_user`.team_id INNER JOIN `team_unit` ON `team_unit`.team_id = `team_user`.team_id WHERE (`team_repo`.repo_id = 653 AND `team_unit`.access_mode >= 2); ``` CREATE INDEX team_unit_access_mode_IDX USING BTREE ON team_unit (access_mode,team_id); | index |id|select_type|table |type|possible_keys |key |key_len|ref |rows|Extra| | ----------------|--|-----------|----------|---|-------------------------|-------------------------|-------|-----|----|-----| |before |1|SIMPLE |team_unit|ALL |UQE_team_unit_s| | | |**113** |Using where; Using temporary| |before | 1|SIMPLE |team_repo|eq_ref|UQE_team_repo_S|UQE_team_repo_S|18 |team_unit.team_id,const|1 |Using index | |before | 1|SIMPLE |team_user|ref |UQE_team_user_S|UQE_team_user_S|9 |team_unit.team_id |82 |Using index | |after| 1|SIMPLE |team_unit|range |UQE_team_unit_s,team_unit_access_mode_IDX|team_unit_access_mode_IDX|5 | |**105** |Using where; Using index; Using temporary| |after| 1|SIMPLE |team_repo|eq_ref|UQE_team_repo_S |UQE_team_repo_S |18 |team_unit.team_id,const|1 |Using index | |after| 1|SIMPLE |team_user|ref |UQE_team_user_S |UQE_team_user_S |9 |team_unit.team_id |82 |Using index | #### Get contributions by user ``` SELECT created_unix DIV 900 * 900 AS timestamp, count(user_id) as contributions FROM `action` WHERE act_user_id IN ( SELECT `user`.id FROM `user` WHERE keep_activity_private = 0 AND visibility = 0) AND repo_id IN ( SELECT id FROM repository WHERE (`repository`.is_private = 0 AND `repository`.owner_id NOT IN ( SELECT id FROM `user` WHERE type = 1 AND visibility IN (2, 1)))) AND user_id = 29 AND act_user_id = 29 AND (created_unix > 1671463009) GROUP BY timestamp ORDER BY timestamp; ``` CREATE INDEX user_type_IDX USING BTREE ON `user` (`type`,visibility); | index |id|select_type|table |type|possible_keys |key |key_len|ref |rows|Extra| | ----------------|--|-----------|----------|---|-------------------------|-------------------------|-------|-----|----|-----| |before | 1|PRIMARY |user |const |PRIMARY |PRIMARY |8 |const |1 |Using temporary; Using filesort| |before | 1|PRIMARY |action |ref |IDX_action_au_r_c_u_d,IDX_action_r_u_d|IDX_action_au_r_c_u_d|9 |const |88088|Using where; Using index | |before| 1|PRIMARY |repository|eq_ref|PRIMARY,IDX_repository_is_private |PRIMARY |8 |action.repo_id|1 |Using where | |before| 4|MATERIALIZED|user |ALL |PRIMARY | | | |**564** |Using where | |after| 1|PRIMARY |user |const |PRIMARY |PRIMARY |8 |const |1 |Using temporary; Using filesort| |after| 1|PRIMARY |action |ref |IDX_action_au_r_c_u_d,IDX_action_r_u_d|IDX_action_au_r_c_u_d|9 |const |88088|Using where; Using index | |after| 1|PRIMARY |repository|eq_ref|PRIMARY,IDX_repository_is_private |PRIMARY |8 |action.repo_id|1 |Using where | |after| 4|MATERIALIZED|user |range |PRIMARY,user_type_IDX |user_type_IDX |9 | |**2** |Using where; Using index | ### Gitea Version 1.19.2 ### Can you reproduce the bug on the Gitea demo site? No ### Operating System Debian 12 / Mariadb 1:10.11.4-1~deb12u1 ### How are you running Gitea? build from source as explained ### Database MySQL/MariaDB

GiteaMirror added the type/bug label 2025-11-02 10:00:15 -06:00

GiteaMirror commented

2025-11-02 10:00:16 -06:00

@lunny commented on GitHub (Dec 5, 2023):

Can you also paste the slow SQL which include mode column in access table?

@lunny commented on GitHub (Dec 5, 2023): Can you also paste the slow SQL which include mode column in access table?

GiteaMirror commented

2025-11-02 10:00:16 -06:00

@camlafit commented on GitHub (Dec 5, 2023):

Hello

I'll try to provide all information to be more accurate possible. I must continue to copy/paste information following slow queries information and patch looking adapted.
About first query I must redo test, as I lost my initial pastebin.

Is why i set this issue as a draft to prevent any other lost :)

@camlafit commented on GitHub (Dec 5, 2023): Hello I'll try to provide all information to be more accurate possible. I must continue to copy/paste information following slow queries information and patch looking adapted. About first query I must redo test, as I lost my initial pastebin. Is why i set this issue as a draft to prevent any other lost :)

GiteaMirror commented

2025-11-02 10:00:16 -06:00

@camlafit commented on GitHub (Dec 19, 2023):

Hello

I've completed my feedback. I've added each sql queries from slow-queries.log.
For each query, I've attached explain before and after alter table to show indexation change.

@camlafit commented on GitHub (Dec 19, 2023): Hello I've completed my feedback. I've added each sql queries from slow-queries.log. For each query, I've attached explain before and after alter table to show indexation change.

Sign in to join this conversation.

Branches Tags

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: github-starred/gitea#12151