When I run a request like this (RT 3.2.2, fastcgi, perl
5.8.4, MySQL 4.0.15, about 5000 tickets):
Status != ‘resolved’ AND ( Requestor.EmailAddress LIKE ‘toto’ OR Cc.EmailAddress LIKE ‘toto’ )
MySQL is stuck in the 'Sending Data' state, I get an
‘Internal Server Error’ on this page and all my subsequent web
requests are blocked (because of the lock on the database). I have
to kill all my MySQL requests (using mysql-admin). A request on
requestor only works fine.
All the other users have no problem because the lock is
tied to a session id.
An index may help but I'm not sure. Has anyone got an idea
on how to make this request work ?
Pierre.
PS: here is the request:
SELECT DISTINCT main.* FROM Tickets main , Groups Groups_1, CachedGroupMembers CachedGroupMembers_2, Users Users_3, Groups Groups_4, CachedGroupMembers CachedGroupMembers_5, Users Users_6 WHERE ((main.EffectiveId = main.id)) AND ((main.Status != ‘deleted’)) AND ((main.Status != ‘resolved’)AND ( ( (Users_3.EmailAddress LIKE ‘%to
to%’)AND(Groups_1.Domain = ‘RT::Ticket-Role’)AND(main.id = Groups_1.Instance)AND(Groups_1.Type = ‘Requestor’)AND(Groups_1.id = CachedGroupMembers_2.GroupId)AND(CachedGroupMembers_2.MemberId = Users_3.id) ) AND ( ( (Users_6.EmailAddress LIKE ‘%toto%’)AND(Groups_4.Domain = ‘RT::Ticket-Role’)AND(main.id = Groups_4.Instance)AND(Groups_4.Type = ‘Cc’)AND(Groups_4.id = CachedGroupMembers_5.GroupId)AND(CachedGroupMembers_5.MemberId = Users_6.id) ) ) ) ) ORDER BY main.id ASC LIMIT 50
and
also:
SELECT COUNT(DISTINCT main.id) FROM Tickets main , Groups Groups_1, CachedGroupMembers CachedGroupMembers_2, Users Users_3, Groups Groups_4, CachedGroupMembers CachedGroupMembers_5, Users Users_6 WHERE ((main.EffectiveId = main.id)) AND ((main.Status != ‘deleted’)) AND ((main.Status != ‘resolved’)AND ( ( (Users_3.EmailAddress LIKE ‘%toto%’)AND(Groups_1.Domain = ‘RT::Ticket-Role’)AND(main.id = Groups_1.Instance)AND(Groups_1.Type = ‘Requestor’)AND(Groups_1.id = CachedGroupMembers_2.GroupId)AND(CachedGroupMembers_2.MemberId = Users_3.id) ) AND ( ( (Users_6.EmailAddress LIKE ‘%toto%’)AND(Groups_4.Domain = ‘RT::Ticket-Role’)AND(main.id = Groups_4.Instance)AND(Groups_4.Type = ‘Cc’)AND(Groups_4.id = CachedGroupMembers_5.GroupId)AND(CachedGroupMembers_5.MemberId = Users_6.id) ) ) ) )
Pierre BLANCHET Support Manager, EMEA
Solsoft, S.A. www.solsoft.com
Phone: +33 1 4715 5502
Email: Pierre.Blanchet@solsoft.com
How secure networks are managed.