“John DeBerry” email@example.com writes:
I am having an elusive problem with RT 2.0.15. It “hangs” sometimes when
displaying a search result
We have a similar problem, with a similar setup. I turned on "slow"
logging on mysql and confirmed that it is searches doing it, usually
pretty hairy ones. Somewhere (can’t remember where) I read that this
behaviour is triggered when a query results in more than one join to
the Watcher’s table. MySQL is not stuck, it’s just doing huge amounts
of processing. If you leave it long enough it will proceed, logging
that it searched thousands of records… Example below, note that
before we killed it it ran for 1020 seconds = 17 minutes on an
vendor_id : GenuineIntel
model name : Intel® Pentium® 4 CPU 1.60GHz
stepping : 4
cpu MHz : 1607.112
cache size : 512 KB
Not sure who’s to blame. Poorly constructed SQL queries from RT, or
MySQL doing something silly?
Still searching for answers myself.
Time: 030124 15:40:53
User@Host: rt2_user[rt2_user] @ localhost 
Query_time: 1020 Lock_time: 0 Rows_sent: 0 Rows_examined: 54377
SELECT DISTINCT main.* FROM Tickets main, Watchers Watchers_1, Watchers Watchers_3 LEFT JOIN Users as Users_2 ON Watchers_1.Owner = Users_2.id LEFT JOIN Users as Users_4 ON Watchers_3.Owner = Users_4.id WHERE ((Watchers_3.Type = ‘Requestor’)) AND ((Watchers_3.Scope = ‘Ticket’)) AND ((main.EffectiveId = main.id)) AND ((Watchers_1.Scope = ‘Ticket’)) AND ((Watchers_1.Type = ‘Requestor’)) AND ((main.Queue = ‘23’)OR(main.Queue = ‘25’)) AND ((Watchers_1.Email LIKE ‘%alfred%’)OR(Users_2.EmailAddress LIKE ‘%alfred%’)OR(Watchers_3.Email LIKE ‘%alfred%’)OR(Users_4.EmailAddress LIKE ‘%alfred%’)) AND main.id = Watchers_1.Value AND main.id = Watchers_3.Value ORDER BY main.id DESC LIMIT 50;