Slow MySQL Query when loading startpage

[gunman808]

Description

Every time the startpage of gitea is loading it took a very long time (>8s). We have activated the mysql-slow-query-log to inspect what is happening and there is a query which tooks a very long time:

SELECT `action`.* FROM `action` INNER JOIN `repository` ON `repository`.id = `action`.repo_id WHERE user_id=7 AND is_deleted=0 ORDER BY `action`.`created_unix` DESC LIMIT 20;
# Time: 2023-11-20T10:19:43.302946Z
# User@Host: gitea[gitea] @  [198.18.4.243]  Id:   266
# Query_time: 8.203707  Lock_time: 0.000028 Rows_sent: 20  Rows_examined: 64560

After using mysql explain to analyze the query, it shows the query is not using the correct index:

mysql> explain SELECT `action`.* FROM `action` INNER JOIN `repository` ON `repository`.id = `action`.repo_id WHERE user_id=7 AND is_deleted=0 ORDER BY `action`.`created_unix` DESC LIMIT 20;
+----+-------------+------------+------------+-------+------------------+------------------------+---------+---------------------------------+------+----------+----------------------------------------------+
| id | select_type | table      | partitions | type  | possible_keys    | key                    | key_len | ref                             | rows | filtered | Extra                                        |
+----+-------------+------------+------------+-------+------------------+------------------------+---------+---------------------------------+------+----------+----------------------------------------------+
|  1 | SIMPLE      | repository | NULL       | index | PRIMARY          | IDX_repository_is_fork | 1       | NULL                            |  170 |   100.00 | Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | action     | NULL       | ref   | IDX_action_r_u_d | IDX_action_r_u_d       | 19      | gitea.repository.id,const,const |  273 |   100.00 | NULL                                         |
+----+-------------+------------+------------+-------+------------------+------------------------+---------+---------------------------------+------+----------+----------------------------------------------+
2 rows in set, 1 warning (0.01 sec)

For testing purpose we removed the index IDX_action_r_u_d and started the explain again:

mysql> drop index IDX_action_r_u_d on action;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain SELECT `action`.* FROM `action` INNER JOIN `repository` ON `repository`.id = `action`.repo_id WHERE user_id=7 AND is_deleted=0 ORDER BY `action`.`created_unix` DESC LIMIT 20;
+----+-------------+------------+------------+--------+---------------+------------------+---------+----------------------+------+----------+-------------+
| id | select_type | table      | partitions | type   | possible_keys | key              | key_len | ref                  | rows | filtered | Extra       |
+----+-------------+------------+------------+--------+---------------+------------------+---------+----------------------+------+----------+-------------+
|  1 | SIMPLE      | action     | NULL       | index  | NULL          | IDX_action_c_u_d | 19      | NULL                 |   20 |     1.00 | Using where |
|  1 | SIMPLE      | repository | NULL       | eq_ref | PRIMARY       | PRIMARY          | 8       | gitea.action.repo_id |    1 |   100.00 | Using index |
+----+-------------+------------+------------+--------+---------------+------------------+---------+----------------------+------+----------+-------------+
2 rows in set, 1 warning (0.01 sec)

Now the query is using the IDX_action_c_u_d and the primary index.
The query is now incredibly fast in comparison. It took
20 rows in set (0.00 sec)
Before removing the index it took 20 rows in set (8.18 sec).

Can you check this behaviour of the index in mysql. Maybe the removed index is needed in other situations.

The workaround of removing the index is working fine for us, but every time restarting the gitea instance, the index will be created again.

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

2.40.1

Operating System

Debian 11

How are you running Gitea?

It is running in the official docker container.
The following images are used:
image: gitea:1.20.5
image: mysql:5.7
image: gitea-runner

Database

MySQL/MariaDB

[katsusan]

In the first case it seems that mysql made a weird decision of using IDX_repository_is_fork when do joining between two tables.

AFAIK, this index probably wouldn't make any work when producing cartesian product between action and repository on the condition of repository.id = action.repo_id. It's hard to understand why mysql chosen to do this(maybe a decison-making mistake of specified version?), however you can try give a index hint like USE INDEX FOR JOIN (`PRIMARY`) and see how will mysql decide to execute.

EXPLAIN SELECT
	`action`.* 
FROM
	`action`
	INNER JOIN 
	`repository` USE INDEX FOR JOIN (`PRIMARY`)
ON `repository`.id = `action`.repo_id
WHERE
	user_id = 7 
	AND is_deleted = 0 
ORDER BY
	`action`.`created_unix` DESC 
LIMIT 20;

[gunman808]

Yes, by giving the index hint the explain shows the using of the PRIMARY index for the repository table:

mysql> EXPLAIN SELECT `action`.*  FROM `action` INNER JOIN  `repository` USE INDEX FOR JOIN (`PRIMARY`) ON `repository`.id = `action`.repo_id WHERE user_id = 7  AND is_deleted = 0  ORDER BY `action`.`created_unix` DESC  LIMIT 20;
+----+-------------+------------+------------+-------+------------------+------------------+---------+---------------------------------+------+----------+----------------------------------------------+
| id | select_type | table      | partitions | type  | possible_keys    | key              | key_len | ref                             | rows | filtered | Extra                                        |
+----+-------------+------------+------------+-------+------------------+------------------+---------+---------------------------------+------+----------+----------------------------------------------+
|  1 | SIMPLE      | repository | NULL       | index | PRIMARY          | PRIMARY          | 8       | NULL                            |  167 |   100.00 | Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | action     | NULL       | ref   | IDX_action_r_u_d | IDX_action_r_u_d | 19      | gitea.repository.id,const,const |  119 |   100.00 | NULL                                         |
+----+-------------+------------+------------+-------+------------------+------------------+---------+---------------------------------+------+----------+----------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

But it doesn't help to optimize the query. It is as slow as before, due to the order in which the query is processed. You can see it by the count of the rows which will be processed.

To change the order of the internal query process, you need to give an index hint (IDX_action_c_u_d) for the action table too.

EXPLAIN SELECT `action`.*  FROM `action` USE INDEX FOR JOIN (`IDX_action_c_u_d`) INNER JOIN  `repository` USE INDEX FOR JOIN (`PRIMARY`) ON `repository`.id = `action`.repo_id WHERE user_id = 7  AND is_deleted = 0  ORDER BY `action`.`created_unix` DESC  LIMIT 20;

Output:

mysql> EXPLAIN SELECT `action`.*  FROM `action` USE INDEX FOR JOIN (`IDX_action_c_u_d`) INNER JOIN  `repository` USE INDEX FOR JOIN (`PRIMARY`) ON `repository`.id = `action`.repo_id WHERE user_id = 7  AND is_deleted = 0  ORDER BY `action`.`created_unix` DESC  LIMIT 20;
+----+-------------+------------+------------+--------+---------------+------------------+---------+----------------------+------+----------+-------------+
| id | select_type | table      | partitions | type   | possible_keys | key              | key_len | ref                  | rows | filtered | Extra       |
+----+-------------+------------+------------+--------+---------------+------------------+---------+----------------------+------+----------+-------------+
|  1 | SIMPLE      | action     | NULL       | index  | NULL          | IDX_action_c_u_d | 19      | NULL                 |   20 |     1.00 | Using where |
|  1 | SIMPLE      | repository | NULL       | eq_ref | PRIMARY       | PRIMARY          | 8       | gitea.action.repo_id |    1 |   100.00 | Using index |
+----+-------------+------------+------------+--------+---------------+------------------+---------+----------------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

Here the table action is processed at first with the where and limit condition and after that it matches against the repository table. There are much less rows processed and so the query is significant faster.
So it would be nice if this query could be fixed with this index hint for the action table.

[42wim]

blocked by https://gitea.com/xorm/xorm/issues/1456 and #16665 also related

[lunny]

I think I can take a look at that.

[lunny]

https://gitea.com/xorm/xorm/pulls/2375 merged. I think we can upgrade xorm and use the new feature.

[lunny]

Can you confirm #28546 fix the problem?

[github-actions]

Automatically locked because of our CONTRIBUTING guidelines