Extremely slow query
Added by Craig Efrein over 14 years ago
Hello,
I am on Redmine 0.9.2 and the search feature is very slow. It takes an average of 3 to 5 seconds to complete a research. All other functionality is fast enough, usually less than a second. The search problem is progressively getting worse with time. There is a definite correlation between the number of records and the time it takes to search. The slowness I think is coming from MySQL. This is because when I run top during the search, MySQL goes to 100% CPU.
I followed the processes with processlist in MySQL and this is what it returned
MySQL Query¶
SELECT count(DISTINCT `issues`.id) AS count_all FROM `issues` LEFT OUTER JOIN `projects` ON `projects`.id = `issues`.project_id LEFT OUTER JOIN `journals` ON `journals`.journalized_id = `issues`.id AND `journals`.journalized_type = 'Issue'WHERE ((projects.status=1 AND projects.id IN (SELECT em.project_id FROM enabled_modules em WHERE em.name='issue_tracking') AND issues.project_id IN (6)) AND (((LOWER(subject) LIKE '%test%') OR (LOWER(issues.description) LIKE '%test%') OR (LOWER(journals.notes) LIKE '%test%'))))
Are there any suggestions for configuring INNODB to be more responsive? I ran a check on the MySQL tables and all was well.
Here is my current configuration :
Current Configuration¶
CentOS 5.2
Redmine 0.9.2
* ruby 1.8.7 (2008-05-31 patchlevel 0) [i686-linux]
* MySQL 5.077
* openssl + openssl-devel
* zlib + zlib-devel
* Lighttpd
* fastcgi
* RubyGems
1. actionmailer (2.3.5)
2. actionpack (2.3.5)
3. activerecord (2.3.5)
4. activeresource (2.3.5)
5. activesupport (2.3.5)
6. cgi_multipart_eof_fix (2.5.0)
7. daemons (1.0.10)
8. fastthread (1.0.7)
9. fcgi (0.8.8)
10. gem_plugin (0.2.3)
11. mongrel (1.1.5)
12. mysql (2.8.1)
13. rack (1.0.1)
14. rails (2.3.5)
15. rake (0.8.7)
16. rmagick (2.0.0)
Thank you in advance for your help