[rt-users] FW: 3.8.1 - New upgrade slow on 'New Search click

[Stuart Browne]

I sent this through a while ago, but got no responses.

Anybody know what can help with this slow 'Search page' coming up issue?

I've since seen a few other people mention the issue, but no response there either..

Anybody?

Stuart

-----Original Message-----
From: Stuart Browne
Sent: Thursday, 4 December 2008 12:06
To: 'rt-users at lists.bestpractical.com'
Subject: 3.8.1 - New upgrade slow on 'New Search click

Hi,

After doing a 3.8.1 upgrade (install into new directory, upgrade db from 3.6.5), clicking on 'Tickets' / 'New Search' causes a 10-15 second pause.

Digging through the active queries when that occurs shows the following query:

SELECT DISTINCT main.* FROM Users main CROSS JOIN ACL ACL_4 JOIN Principals Principals_1  ON ( Principals_1.id = main.id ) JOIN CachedGroupMembers CachedGroupMembers_2  ON ( CachedGroupMembers_2.MemberId = Principals_1.id ) JOIN Groups Groups_3  ON ( Groups_3.id = CachedGroupMembers_2.GroupId )  WHERE (Principals_1.Disabled = '0') AND (ACL_4.PrincipalType = Groups_3.Type) AND (Principals_1.id != '1') AND (Principals_1.PrincipalType = 'User') AND (ACL_4.RightName = 'OwnTicket' OR ACL_4.RightName = 'SuperUser') AND (Groups_3.Domain = 'RT::Queue-Role') AND ((ACL_4.ObjectType = 'RT::Queue') OR (ACL_4.ObjectType = 'RT::System'))  ORDER BY main.Name ASC

Eww.

Anyway.  The explain on this shows:

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: main
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 65
        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,CachedGroupMembers3
          key: CachedGroupMembers3
      key_len: 5
          ref: rt3.Principals_1.id
         rows: 1
        Extra: Using where; Distinct
*************************** 4. row ***************************
           id: 1
  select_type: SIMPLE
        table: ACL_4
         type: range
possible_keys: ACL1
          key: ACL1
      key_len: 54
          ref: NULL
         rows: 82
        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

Using google, two possible matches were found.

- One of the queues has a 'Group Right' allowing 'Everyone' to 'Own Ticket'.  I have gone through all our queues, found one queue with this permission.  Removed the permission, issue still persists.  I've even gone through and verified disabled queue's group rights.

- The explain shows 'Using filesort' and the 'tmp_table_size' should be increased.  Changing this to 4 times the default has had no effect.  This coupled with the fact that our DB isn't that big.

So, I was wondering if anybody else was experiencing this pause, and preferably if anybody knows what's causing it so I can fix it.

Thanks

Stuart J. Browne