The problem seems to be caused by the ORDER BY clause. If I remove this, it's very fast. PK
________________________________ From: Jesse Vincent [mailto:[EMAIL PROTECTED] Sent: Thursday, April 26, 2007 6:00 AM To: Todd Chapman Cc: Philip Kime; RT Users Subject: Re: [rt-users] Slow ticket search page becoming a problem 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: [EMAIL PROTECTED] Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com
_______________________________________________ http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users Community help: http://wiki.bestpractical.com Commercial support: [EMAIL PROTECTED] Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com
