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

[Jesse Vincent]

That looks suspiciously like the problem I'd been talking to you  
about before, Todd.

On Apr 25, 2007, at 11:27 PM, Philip Kime wrote:

> 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
>
> _______________________________________________
> http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
>
> Community help: http://wiki.bestpractical.com
> Commercial support: sales at bestpractical.com
>
>
> Discover RT's hidden secrets with RT Essentials from O'Reilly Media.
> Buy a copy at http://rtbook.bestpractical.com

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20070426/188a7483/attachment.htm>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: PGP.sig
Type: application/pgp-signature
Size: 186 bytes
Desc: This is a digitally signed message part
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20070426/188a7483/attachment.sig>