Issues query optimization. How to redefine scope?

Added by Sergei Popov about 3 years ago

I use Redmine 3 and have the optimisation problem, when view all issues. I have seen Redmine 4 and have seen some problem, but use EXISTS in subquery.
My query

SELECT COUNT(*) 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 projects.id IN (SELECT em.project_id FROM enabled_modules em WHERE em.name='issue_tracking')) AND ((projects.id IN (22,23,24,25,27,31,32,34,35,36,46,47,64,65,66,67) AND (1=1))))) AND ((issues.status_id IN (SELECT id FROM issue_statuses WHERE is_closed=0)))

has executed about 26 seconds, becouse have subquery with enambled_modules.
Redmine 4 has some different query and executeda little bit faster - 25 second.
SELECT COUNT(*) 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.id IN (22,23,24,25,27,31,32,34,35,36,46,47,64,65,66,67) AND (1=1))))) AND ((issues.status_id IN (SELECT id FROM issue_statuses WHERE is_closed=0)))

I have try to optimize this query and write

SELECT COUNT(*) FROM `issues` INNER JOIN `projects` ON `projects`.`id` = `issues`.`project_id` INNER JOIN `issue_statuses` ON `issue_statuses`.`id` = `issues`.`status_id` 
INNER JOIN enabled_modules em ON projects.id = em.project_id AND em.name='issue_tracking' 
WHERE (((projects.status <> 9) AND ((projects.id IN (22,23,24,25,27,31,32,34,35,36,46,47,64,65,66,67) AND (1=1))))) AND ((issues.status_id IN (SELECT id FROM issue_statuses WHERE is_closed=0)))

This query is executed 4 second.

Then I write plugin, where change scopes and make join with enabled_modules table.
Scope was redefined for Version and Tracker, but for Issue I redefine scope in plugin. but always called scope from Redmine.
I tried to delete scope with
singleton_class.send(:remove_method, :visible)
and then added my scope, but Redmine not redefine my scope.
What maybe problem?
And what are you thinking about optimization? Could I have problems with such join? How to replace subquery to join for better performance?
Files for patch in:
[[https://gist.github.com/Casual3498/8b196a8782d61b9f6fccc993ec891e9b]]