Vote #80541
未完了A inner join can be removed
ステータス:
New
優先度:
通常
担当者:
-
カテゴリ:
-
対象バージョン:
-
開始日:
2022/05/09
期日:
進捗率:
0%
予定工数:
Redmineorg_URL:
category_id:
0
version_id:
0
issue_org_id:
32610
author_id:
306584
assigned_to_id:
0
comments:
0
status_id:
1
tracker_id:
1
plus1:
0
affected_version:
closed_on:
affected_version_id:
説明
There is a query
SELECT DISTINCT(issues.id) FROM issues INNER JOIN projects ON projects.id = issues.project_id INNER JOIN issue_statuses ON issue_statuses.id = issues.status_id LEFT OUTER JOIN users ON users.id = issues.assigned_to_id LEFT OUTER JOIN trackers ON trackers.id = issues.tracker_id LEFT OUTER JOIN enumerations ON enumerations.id = issues.priority_id AND enumerations.type IN ('IssuePriority') LEFT OUTER JOIN versions ON versions.id = issues.priority_id WHERE (projects.status <> 9 AND projects.id IN (SELECT em.project_id FROM enabled_modules em WHERE em.name='issue_tracking')) AND ((issues.status_id IN (SELECT id FROM issue_statuses WHERE is_closed=0)) AND projects.id = 3821) ORDER BY issues.id DESC LIMIT 501;
which actually can be simplied due to the foreign key and presence constraint
-- remove LEFT OUTER JOIN (because of id being PK, already done by postgres)
-- remove INNER JOIN (because of FK; no need to add issues.project_id IS NOT NULL / issues.status_id IS NOT NULL because of constraint)
-- remove DISTINCT (FK)
After removing, the query becomes:
SELECT issues.id FROM issues INNER JOIN projects ON projects.id = issues.project_id LEFT OUTER JOIN enumerations ON enumerations.id = issues.priority_id AND enumerations.type IN ('IssuePriority') WHERE (projects.status <> 9 AND projects.id IN (SELECT em.project_id FROM enabled_modules em WHERE em.name='issue_tracking')) AND ((issues.status_id IN (SELECT id FROM issue_statuses WHERE is_closed=0)) AND projects.id = 3821) ORDER BY issues.id DESC LIMIT 501;
It can make the code easier to read and faster
表示するデータがありません