[rt-devel] Another slow MySQL query...

[Harald Wagener]

With rt 3.0.3 and MySQL 4.0.13, the query below runs for more than 10
minutes without returning. It is done when searching for more than one
requestor email address.

Anybody share some insight as how to speed this up?

Regards,
    Harald

SELECT count(DISTINCT main.id)
  FROM Tickets main, Groups Groups_1, Principals Principals_2,
CachedGroupMembers
     CachedGroupMembers_3, Principals Principals_4, Users Users_5,
Groups Groups_6, Principals
     Principals_7, CachedGroupMembers CachedGroupMembers_8, Principals
Principals_9, Users Users_10
  WHERE ((main.EffectiveId = main.id)) AND ((main.Type = 'ticket')) AND
((((Users_5.EmailAddress
     LIKE '%behle%')AND(Groups_1.Domain =
'RT::Ticket-Role')AND(Groups_1.Type =
     'Requestor')AND(Principals_2.PrincipalType = 'Group')) OR
((Users_10.EmailAddress LIKE
     '%wagener%')AND(Groups_6.Domain =
'RT::Ticket-Role')AND(Groups_6.Type =
     'Requestor')AND(Principals_7.PrincipalType = 'Group'))) AND
((main.Owner = '10')OR(main.Owner
     = '330')) AND ((main.Status = 'new')OR(main.Status = 'open')) AND
((main.Queue = '5'))) AND
     Groups_1.Instance = main.id AND Groups_1.id = Principals_2.ObjectId
AND Principals_2.id =
     CachedGroupMembers_3.GroupId AND CachedGroupMembers_3.MemberId =
Principals_4.id AND
     Principals_4.ObjectId = Users_5.id AND Groups_6.Instance = main.id
AND Groups_6.id =
     Principals_7.ObjectId AND Principals_7.id =
CachedGroupMembers_8.GroupId AND
     CachedGroupMembers_8.MemberId = Principals_9.id AND
Principals_9.ObjectId = Users_10.id