[rt-users] Really slow Search page startup suddenly

[Philip Kime]

It's happening all the time now for some reason. This is really odd - it
just seems to have started - here's a typical example - state is
"Copying to tmp table" during the execution:

# Time: 070406 17:25:05
# User at Host: rt_user[rt_user] @ localhost []
# Query_time: 38  Lock_time: 0  Rows_sent: 288  Rows_examined: 54320878

mysql> explain SELECT DISTINCT main.* FROM Users main CROSS JOIN ACL
ACL_4 JOIN
Principals Principals_1  ON ( Principals_1.id = main.id ) JOIN
CachedGroupMember
s CachedGroupMembers_2  ON ( CachedGroupMembers_2.MemberId =
Principals_1.id ) J
OIN Groups Groups_3  ON ( Groups_3.id = CachedGroupMembers_2.GroupId )
WHERE (P
rincipals_1.Disabled = '0') AND (ACL_4.PrincipalType = Groups_3.Type)
AND (Princ
ipals_1.id != '1') AND (Principals_1.PrincipalType = 'User') AND
(ACL_4.RightNam
e = 'OwnTicket') AND ((ACL_4.ObjectType = 'RT::Queue') OR
(ACL_4.ObjectType = 'R
T::System')) AND ((Groups_3.Domain = 'RT::Queue-Role') OR
(Groups_3.Domain = 'RT
::System-Role'))  ORDER BY main.RealName ASC\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: main
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 696
        Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: main
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 696
        Extra: Using where; Using temporary; Using filesort
*************************** 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
5 rows in set (0.01 sec)