Searching very slow
Added by Andrej Simko almost 11 years ago
Hello,
we have problem with performance in redmine, our environment is:
Environment:
Redmine version 2.4.1.stable
Ruby version 1.8.7-p371 (2012-10-12) [i686-linux]
Rails version 3.2.15
Environment production
Database adapter Mysql2
SET timestamp=1392978992;
SELECT COUNT FROM `issues` LEFT OUTER JOIN `projects` ON `projects`.`id` = `issues`.`project_id` LEFT OUTER JOIN `journals` ON `journals`.`journalized_id` = `issues`.`id` AND (journals.private_notes = 0 OR (projects.status <> 9 AND projects.id IN (SELECT em.project_id FROM enabled_modules em WHERE em.name='issue_tracking'))) AND `journals`.`journalized_type` = 'Issue' WHERE (projects.status <> 9 AND projects.id IN (SELECT em.project_id FROM enabled_modules em WHERE em.name='issue_tracking')) AND (((LOWER LIKE '%pulseaudio%') OR (LOWER LIKE '%pulseaudio%') OR (LOWER LIKE '%pulseaudio%') OR issues.id IN (SELECT customized_id FROM custom_values WHERE customized_type='Issue' AND customized_id=issues.id AND LOWER LIKE '%pulseaudio%' AND custom_values.custom_field_id = 3) AND ((1=1) AND (issues.tracker_id IN (SELECT tracker_id FROM custom_fields_trackers WHERE custom_field_id = 3) AND issues.project_id IN (SELECT project_id FROM custom_fields_projects WHERE custom_field_id = 3))) OR issues.id IN (SELECT customized_id FROM custom_values WHERE customized_type='Issue' AND customized_id=issues.id AND LOWER LIKE '%pulseaudio%' AND custom_values.custom_field_id = 7) AND ((1=1) AND (issues.tracker_id IN (SELECT tracker_id FROM custom_fields_trackers WHERE custom_field_id = 7) AND issues.project_id IN (SELECT project_id FROM custom_fields_projects WHERE custom_field_id = 7))) OR issues.id IN (SELECT customized_id FROM custom_values WHERE customized_type='Issue' AND customized_id=issues.id AND LOWER LIKE '%pulseaudio%' AND custom_values.custom_field_id = 10) AND ((1=1) AND (issues.tracker_id IN (SELECT tracker_id FROM custom_fields_trackers WHERE custom_field_id = 10) AND issues.project_id IN (SELECT project_id FROM custom_fields_projects WHERE custom_field_id = 10))) OR issues.id IN (SELECT customized_id FROM custom_values WHERE customized_type='Issue' AND customized_id=issues.id AND LOWER LIKE '%pulseaudio%' AND custom_values.custom_field_id = 15) AND ((1=1) AND (issues.tracker_id IN (SELECT tracker_id FROM custom_fields_trackers WHERE custom_field_id = 15) AND issues.project_id IN (SELECT project_id FROM custom_fields_projects WHERE custom_field_id = 15))) OR issues.id IN (SELECT customized_id FROM custom_values WHERE customized_type='Issue' AND customized_id=issues.id AND LOWER LIKE '%pulseaudio%' AND custom_values.custom_field_id = 17) AND ((1=1) AND (issues.tracker_id IN (SELECT tracker_id FROM custom_fields_trackers WHERE custom_field_id = 17) AND issues.project_id IN (SELECT project_id FROM custom_fields_projects WHERE custom_field_id = 17))) OR issues.id IN (SELECT customized_id FROM custom_values WHERE customized_type='Issue' AND customized_id=issues.id AND LOWER LIKE '%pulseaudio%' AND custom_values.custom_field_id = 24) AND ((1=1) AND (issues.tracker_id IN (SELECT tracker_id FROM custom_fields_trackers WHERE custom_field_id = 24) AND issues.project_id IN (SELECT project_id FROM custom_fields_projects WHERE custom_field_id = 24))) OR issues.id IN (SELECT customized_id FROM custom_values WHERE customized_type='Issue' AND customized_id=issues.id AND LOWER LIKE '%pulseaudio%' AND custom_values.custom_field_id = 23) AND ((1=1) AND (issues.tracker_id IN (SELECT tracker_id FROM custom_fields_trackers WHERE custom_field_id = 23) AND issues.project_id IN (SELECT project_id FROM custom_fields_projects WHERE custom_field_id = 23))) OR issues.id IN (SELECT customized_id FROM custom_values WHERE customized_type='Issue' AND customized_id=issues.id AND LOWER LIKE '%pulseaudio%' AND custom_values.custom_field_id = 25) AND ((1=1) AND (issues.tracker_id IN (SELECT tracker_id FROM custom_fields_trackers WHERE custom_field_id = 25) AND issues.project_id IN (SELECT project_id FROM custom_fields_projects WHERE custom_field_id = 25))) OR issues.id IN (SELECT customized_id FROM custom_values WHERE customized_type='Issue' AND customized_id=issues.id AND LOWER LIKE '%pulseaudio%' AND custom_values.custom_field_id = 27) AND ((1=1) AND (issues.tracker_id IN (SELECT tracker_id FROM custom_fields_trackers WHERE custom_field_id = 27) AND issues.project_id IN (SELECT project_id FROM custom_fields_projects WHERE custom_field_id = 27))) OR issues.id IN (SELECT customized_id FROM custom_values WHERE customized_type='Issue' AND customized_id=issues.id AND LOWER LIKE '%pulseaudio%' AND custom_values.custom_field_id = 33) AND ((1=1) AND (issues.tracker_id IN (SELECT tracker_id FROM custom_fields_trackers WHERE custom_field_id = 33))) OR issues.id IN (SELECT customized_id FROM custom_values WHERE customized_type='Issue' AND customized_id=issues.id AND LOWER LIKE '%pulseaudio%' AND custom_values.custom_field_id = 42) AND ((1=1) AND (issues.tracker_id IN (SELECT tracker_id FROM custom_fields_trackers WHERE custom_field_id = 42) AND issues.project_id IN (SELECT project_id FROM custom_fields_projects WHERE custom_field_id = 42)))));
- Time: 140221 11:37:09
- User@Host: redmine[redmine] @ localhost []
- Query_time: 37.304327 Lock_time: 0.000815 Rows_sent: 11 Rows_examined: 1318748
SET timestamp=1392979029;
SELECT DISTINCT `issues`.id FROM `issues` LEFT OUTER JOIN `projects` ON `projects`.`id` = `issues`.`project_id` LEFT OUTER JOIN `journals` ON `journals`.`journalized_id` = `issues`.`id` AND (journals.private_notes = 0 OR (projects.status <> 9 AND projects.id IN (SELECT em.project_id FROM enabled_modules em WHERE em.name='issue_tracking'))) AND `journals`.`journalized_type` = 'Issue' WHERE (projects.status <> 9 AND projects.id IN (SELECT em.project_id FROM enabled_modules em WHERE em.name='issue_tracking')) AND (((LOWER LIKE '%pulseaudio%') OR (LOWER LIKE '%pulseaudio%') OR (LOWER LIKE '%pulseaudio%') OR issues.id IN (SELECT customized_id FROM custom_values WHERE customized_type='Issue' AND customized_id=issues.id AND LOWER LIKE '%pulseaudio%' AND custom_values.custom_field_id = 3) AND ((1=1) AND (issues.tracker_id IN (SELECT tracker_id FROM custom_fields_trackers WHERE custom_field_id = 3) AND issues.project_id IN (SELECT project_id FROM custom_fields_projects WHERE custom_field_id = 3))) OR issues.id IN (SELECT customized_id FROM custom_values WHERE customized_type='Issue' AND customized_id=issues.id AND LOWER LIKE '%pulseaudio%' AND custom_values.custom_field_id = 7) AND ((1=1) AND (issues.tracker_id IN (SELECT tracker_id FROM custom_fields_trackers WHERE custom_field_id = 7) AND issues.project_id IN (SELECT project_id FROM custom_fields_projects WHERE custom_field_id = 7))) OR issues.id IN (SELECT customized_id FROM custom_values WHERE customized_type='Issue' AND customized_id=issues.id AND LOWER LIKE '%pulseaudio%' AND custom_values.custom_field_id = 10) AND ((1=1) AND (issues.tracker_id IN (SELECT tracker_id FROM custom_fields_trackers WHERE custom_field_id = 10) AND issues.project_id IN (SELECT project_id FROM custom_fields_projects WHERE custom_field_id = 10))) OR issues.id IN (SELECT customized_id FROM custom_values WHERE customized_type='Issue' AND customized_id=issues.id AND LOWER LIKE '%pulseaudio%' AND custom_values.custom_field_id = 15) AND ((1=1) AND (issues.tracker_id IN (SELECT tracker_id FROM custom_fields_trackers WHERE custom_field_id = 15) AND issues.project_id IN (SELECT project_id FROM custom_fields_projects WHERE custom_field_id = 15))) OR issues.id IN (SELECT customized_id FROM custom_values WHERE customized_type='Issue' AND customized_id=issues.id AND LOWER LIKE '%pulseaudio%' AND custom_values.custom_field_id = 17) AND ((1=1) AND (issues.tracker_id IN (SELECT tracker_id FROM custom_fields_trackers WHERE custom_field_id = 17) AND issues.project_id IN (SELECT project_id FROM custom_fields_projects WHERE custom_field_id = 17))) OR issues.id IN (SELECT customized_id FROM custom_values WHERE customized_type='Issue' AND customized_id=issues.id AND LOWER LIKE '%pulseaudio%' AND custom_values.custom_field_id = 24) AND ((1=1) AND (issues.tracker_id IN (SELECT tracker_id FROM custom_fields_trackers WHERE custom_field_id = 24) AND issues.project_id IN (SELECT project_id FROM custom_fields_projects WHERE custom_field_id = 24))) OR issues.id IN (SELECT customized_id FROM custom_values WHERE customized_type='Issue' AND customized_id=issues.id AND LOWER LIKE '%pulseaudio%' AND custom_values.custom_field_id = 23) AND ((1=1) AND (issues.tracker_id IN (SELECT tracker_id FROM custom_fields_trackers WHERE custom_field_id = 23) AND issues.project_id IN (SELECT project_id FROM custom_fields_projects WHERE custom_field_id = 23))) OR issues.id IN (SELECT customized_id FROM custom_values WHERE customized_type='Issue' AND customized_id=issues.id AND LOWER LIKE '%pulseaudio%' AND custom_values.custom_field_id = 25) AND ((1=1) AND (issues.tracker_id IN (SELECT tracker_id FROM custom_fields_trackers WHERE custom_field_id = 25) AND issues.project_id IN (SELECT project_id FROM custom_fields_projects WHERE custom_field_id = 25))) OR issues.id IN (SELECT customized_id FROM custom_values WHERE customized_type='Issue' AND customized_id=issues.id AND LOWER LIKE '%pulseaudio%' AND custom_values.custom_field_id = 27) AND ((1=1) AND (issues.tracker_id IN (SELECT tracker_id FROM custom_fields_trackers WHERE custom_field_id = 27) AND issues.project_id IN (SELECT project_id FROM custom_fields_projects WHERE custom_field_id = 27))) OR issues.id IN (SELECT customized_id FROM custom_values WHERE customized_type='Issue' AND customized_id=issues.id AND LOWER LIKE '%pulseaudio%' AND custom_values.custom_field_id = 33) AND ((1=1) AND (issues.tracker_id IN (SELECT tracker_id FROM custom_fields_trackers WHERE custom_field_id = 33))) OR issues.id IN (SELECT customized_id FROM custom_values WHERE customized_type='Issue' AND customized_id=issues.id AND LOWER LIKE '%pulseaudio%' AND custom_values.custom_field_id = 42) AND ((1=1) AND (issues.tracker_id IN (SELECT tracker_id FROM custom_fields_trackers WHERE custom_field_id = 42) AND issues.project_id IN (SELECT project_id FROM custom_fields_projects WHERE custom_field_id = 42))))) ORDER BY issues.id DESC LIMIT 11;
Database has something about 200000 rows, but is it normal to search in them with joins and the value of rows is more than milion?
Second thing I dont understand is that in one search there are two selects, for what?
Is there a thing, that we could use to have searches faster?
Thank you for any answer.
Replies (2)
RE: Searching very slow - Added by Ilyas Makashev over 10 years ago
Same problem here. About 1.5M issues in database. Searching by username at homepage takes more than 15 minutes.
Version of redmine 2.4.5-stable
RE: Searching very slow - Added by Carolina Sandoval over 10 years ago
Same here! Redmine version 2.5.1 bdd Postgres. Any suggestions to make it fast?