Searching very slow

Added by Andrej Simko over 7 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

Searching in database is very slow, this is from our slow queries log:
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)))));
  1. Time: 140221 11:37:09
  2. User@Host: redmine[redmine] @ localhost []
  3. 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 almost 7 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 almost 7 years ago

Same here! Redmine version 2.5.1 bdd Postgres. Any suggestions to make it fast?

(1-2/2)