[rt-users] RT3 performance on `reply' again

[Christian Zagrodnick]

Hi there

I still have with this crasy query Jamie Wilkinson already noted in 
http://lists.fsck.com/pipermail/rt-users/2003-April/013222.html

I installed the latest SearchBuilder and it is still way slow.

The query issued is:
 SELECT DISTINCT main.* FROM Users main  JOIN Principals as Principals_3  ON  main.id = Principals_3.id JOIN
   CachedGroupMembers as CachedGroupMembers_6  ON  Principals_3.Id = CachedGroupMembers_6.MemberId JOIN Principals as Principals_1
   ON  main.id = Principals_1.id JOIN CachedGroupMembers as CachedGroupMembers_7  ON  Principals_1.id =
   CachedGroupMembers_7.MemberId JOIN Principals as Principals_4  ON  CachedGroupMembers_6.GroupId = Principals_4.Id, Groups
   Groups_2, ACL ACL_5  WHERE ((ACL_5.RightName = 'SuperUser')OR(ACL_5.RightName = 'OwnTicket')) AND
  ((CachedGroupMembers_7.GroupId = '4')) AND ((Principals_1.Disabled = '0')) AND ((Principals_1.PrincipalType = 'User')) AND
  ((Principals_3.PrincipalType = 'User')) AND (ACL_5.ObjectType = 'RT::System'  OR (ACL_5.ObjectType = 'RT::Queue'  AND
  ACL_5.ObjectId = '10') ) AND ( (ACL_5.PrincipalId = Principals_4.Id AND Principals_4.id = Groups_2.Id AND ACL_5.PrincipalType =
  'Group' AND (Groups_2.Domain = 'SystemInternal' OR Groups_2.Domain = 'UserDefined' OR Groups_2.Domain = 'ACLEquivalence'))  OR
  ( ( (Groups_2.Domain = 'RT::Queue-Role' AND Groups_2.Instance = '10')  OR ( Groups_2.Domain = 'RT::Ticket-Role' AND
   Groups_2.Instance = '4344')  )  AND Groups_2.Type = ACL_5.PrincipalType AND Groups_2.Id = Principals_4.id AND
  Principals_4.PrincipalType = 'Group') )  ORDER BY main.Name ASC
 
It takes 8 minutes; the database is vacuumed, no swapping. Only the CPU
is crunching.

This database is imported from rt2 tickets. There are about 4500 Tickets,
the principals table contains about 18500 entries, groups about 17500.

-- 
Christian Zagrodnick

gocept gmbh & co. kg - schalaunische strasse 6 - 06366 koethen/anhalt
fon. +49 3496 3099112, +49 179 1463644
fax. +49 3496 3099118