Project

General

Profile

Actions

Defect #32610

open

A inner join can be removed

Added by jwjw yy almost 5 years ago.

Status:
New
Priority:
Normal
Assignee:
-
Category:
-
Target version:
-
Start date:
Due date:
% Done:

0%

Estimated time:
Resolution:
Affected version:

Description

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

No data to display

Actions

Also available in: Atom PDF