[rt-users] Searching with RT "hangs"

[Giles Malet - IST]

"John DeBerry" <jdeberry at townnews.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(R) Pentium(R) 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.

gdm


# Time: 030124 15:40:53
# User at 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;