Need help whith sql query

Added by Alexandr Chernyaev about 1 month ago

SQL query runs for hours on Redmine 3.4 and MariaDB

I will be glad to any ideas

SELECT
`issues`.`id`
FROM `issues`
INNER JOIN `projects`
ON `projects`.`id` = `issues`.`project_id`
INNER JOIN `issue_statuses`
ON `issue_statuses`.`id` = `issues`.`status_id`
WHERE ((((projects.status <> 9
AND EXISTS (SELECT
1 AS one
FROM enabled_modules em
WHERE em.project_id = projects.id
AND em.name = 'issue_tracking'))
AND (((projects.is_public = 1
AND projects.id NOT IN (SELECT
project_id
FROM members
WHERE user_id IN (70, 210)))
AND ((issues.is_private = 0
OR issues.author_id = 70
OR issues.assigned_to_id IN (70, 88, 199, 318))))
OR (projects.id IN (2, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 16, 17, 18, 19, 20, 21, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62)
AND (1 = 1))
OR (projects.id IN (2, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61)
AND (1 = 1))
OR (projects.id IN (2, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61)
AND (1 = 1))
OR (projects.id IN (2, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62)
AND (1 = 1))))))
AND (projects.lft >= 103
AND projects.rgt <= 106
AND ((issues.status_id IS NULL
OR issues.status_id NOT IN ('5', '6', '7', '3')))
AND issues.id IN (SELECT
issues.id
FROM issues
LEFT OUTER JOIN custom_values
ON custom_values.customized_type = 'Issue'
AND custom_values.customized_id = issues.id
AND custom_values.custom_field_id = 2
WHERE (custom_values.value IN ('82', '4'))
AND (((1 = 1)
AND (issues.tracker_id IN (SELECT
tracker_id
FROM custom_fields_trackers
WHERE custom_field_id = 2))
AND (EXISTS (SELECT
1
FROM custom_fields ifa
WHERE ifa.is_for_all = 1
AND ifa.id = 2)
OR issues.project_id IN (SELECT
project_id
FROM custom_fields_projects
WHERE custom_field_id = 2)))))
AND issues.id IN (SELECT
issues.id
FROM issues
LEFT OUTER JOIN custom_values
ON custom_values.customized_type = 'Issue'
AND custom_values.customized_id = issues.id
AND custom_values.custom_field_id = 16
WHERE (custom_values.value IS NOT NULL
AND custom_values.value <> '')
AND (((issues.project_id IN (SELECT DISTINCT
m.project_id
FROM members m
INNER JOIN member_roles mr
ON mr.member_id = m.id
INNER JOIN custom_fields_roles cfr
ON cfr.role_id = mr.role_id
WHERE m.user_id = 70
AND cfr.custom_field_id = 16))
AND (issues.tracker_id IN (SELECT
tracker_id
FROM custom_fields_trackers
WHERE custom_field_id = 16))
AND (EXISTS (SELECT
1
FROM custom_fields ifa
WHERE ifa.is_for_all = 1
AND ifa.id = 16)
OR issues.project_id IN (SELECT
project_id
FROM custom_fields_projects
WHERE custom_field_id = 16)))))
AND issues.id IN (SELECT
issues.id
FROM issues
LEFT OUTER JOIN custom_values
ON custom_values.customized_type = 'Issue'
AND custom_values.customized_id = issues.id
AND custom_values.custom_field_id = 15
WHERE (custom_values.value IS NOT NULL
AND custom_values.value <> '')
AND (((issues.project_id IN (SELECT DISTINCT
m.project_id
FROM members m
INNER JOIN member_roles mr
ON mr.member_id = m.id
INNER JOIN custom_fields_roles cfr
ON cfr.role_id = mr.role_id
WHERE m.user_id = 70
AND cfr.custom_field_id = 15))
AND (issues.tracker_id IN (SELECT
tracker_id
FROM custom_fields_trackers
WHERE custom_field_id = 15))
AND (EXISTS (SELECT
1
FROM custom_fields ifa
WHERE ifa.is_for_all = 1
AND ifa.id = 15)
OR issues.project_id IN (SELECT
project_id
FROM custom_fields_projects
WHERE custom_field_id = 15))))))

Replies (1)

RE: Need help whith sql query - Added by Rodrigo Vilar about 1 month ago

Hi Alexandr,

The SQL Query is too complex. I have two suggestions:
1) reconsider the Database structure you should avoid to have checks like this in your query, perhapscustom_values could directly have a issues_id that might be null, and the join is enough.
ON custom_values.customized_type = 'Issue'
AND custom_values.customized_id = issues.id
AND custom_values.custom_field_id = 16
2) avoid overlapping ORs and always true conditions (1=1)
OR (projects.id IN (2, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61)
AND (1 = 1))
OR (projects.id IN (2, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61)
AND (1 = 1))
OR (projects.id IN (2, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62)
AND (1 = 1))))))

(1-1/1)