Merge Request taking long time
After upgrading the GitLab version from 9.2.2 to 9.5.0 we found the below given query is taking too long time to complete.
Query is
SELECT DISTINCT(sha) FROM merge_request_diff_commits WHERE merge_request_diff_commits.merge_request_diff_id IN (SELECT merge_request_diffs.id FROM merge_request_diffs WHERE merge_request_diffs.merge_request_id = 100554 ORDER BY merge_request_diffs.id DESC)
explain command output for the above query
±—±-------------------±---------------------------±----------------±------------------------------------------------------±--------±--------±-----±--------±-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
±—±-------------------±---------------------------±----------------±------------------------------------------------------±--------±--------±-----±--------±-----------------------------+
| 1 | PRIMARY | merge_request_diff_commits | ALL | NULL | NULL | NULL | NULL | 2895265 | Using where; Using temporary |
| 2 | DEPENDENT SUBQUERY | merge_request_diffs | unique_subquery | PRIMARY,index_merge_request_diffs_on_merge_request_id | PRIMARY | 4 | func | 1 | Using where |
±—±-------------------±---------------------------±----------------±------------------------------------------------------±--------±--------±-----±--------±-----------------------------+
Also, I’ve attached the show processlist command output.
Kindly let me know the solution.