[rt-users] Slow ticket search page becoming a problem

[Philip Kime]

I know I asked this before but I've been swamped and lost track of there
the discussion got to.
 
RT 3.6.3, Mysql 5.0.27. The main ticket search page is really slow to
load, typically 45 seconds, sometimes longer. Problem query and explain
below. It's starting to become a problem for us. Seems that the first
row of the explain output is the guilty one. Rows_examined is absurdly
high.
 
PK
 
# Query_time: 45  Lock_time: 0  Rows_sent: 290  Rows_examined: 65256162
SELECT DISTINCT main.* FROM Users main , Principals Principals_1,
CachedGroupMembers CachedGroupMembers_2, Groups Groups_3, ACL ACL_4
WHERE ((ACL_4.PrincipalType = Groups_3.Type)) AND ((ACL_4.RightName =
'OwnTicket')) AND ((CachedGroupMembers_2.MemberId = Principals_1.id))
AND ((Groups_3.id = CachedGroupMembers_2.GroupId)) AND
((Principals_1.Disabled = '0')) AND ((Principals_1.PrincipalType =
'User')) AND ((Principals_1.id != '1')) AND ((main.id =
Principals_1.id)) AND ((ACL_4.ObjectType = 'RT::Queue') OR
(ACL_4.ObjectType = 'RT::System')) AND ((Groups_3.Domain =
'RT::Queue-Role') OR (Groups_3.Domain = 'RT::System-Role'))  ORDER BY
main.RealName ASC;
 
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: main
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 673
        Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: Principals_1
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: rt3.main.id
         rows: 1
        Extra: Using where; Distinct
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: CachedGroupMembers_2
         type: ref
possible_keys: DisGrouMem,SHRD_CGM1
          key: SHRD_CGM1
      key_len: 5
          ref: rt3.main.id
         rows: 1
        Extra: Using where; Using index; Distinct
*************************** 4. row ***************************
           id: 1
  select_type: SIMPLE
        table: ACL_4
         type: range
possible_keys: ACL1
          key: ACL1
      key_len: 54
          ref: NULL
         rows: 77
        Extra: Using where; Using index; Distinct
*************************** 5. row ***************************
           id: 1
  select_type: SIMPLE
        table: Groups_3
         type: eq_ref
possible_keys: PRIMARY,Groups1,Groups2
          key: PRIMARY
      key_len: 4
          ref: rt3.CachedGroupMembers_2.GroupId
         rows: 1
        Extra: Using where; Distinct

 
 
--
Philip Kime
NOPS Systems Architect
310 401 0407
 
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20070425/1d9a68dd/attachment.htm>