Ok, the issue is that MYSQL 5 won't use the index on main.Name by default (possible keys list PRIMARY only, which is useless for this ORDER BY clause), which it really needs to do with an ORDER BY clause for main.Name (or main.RealName as in my example as I have modified the display code). It is fixed if you force the index use: mysql> SELECT DISTINCT main.* FROM Principals Principals_1, CachedGroupMembers C achedGroupMembers_2, Groups Groups_3, ACL ACL_4, Users main FORCE INDEX(Users1) WHERE ((ACL_4.PrincipalType = Groups_3.Type)) AND ((ACL_4.RightName = 'Own Ticket')) AND ((CachedGroupMembers_2.MemberId = Principals_1.id)) AND ((Groups_3 .id = CachedGroupMembers_2.GroupId)) AND ((Principals_1.Disabled = '0')) AND ((P rincipals_1.PrincipalType = 'User')) AND ((Principals_1.id != '1')) AND ((main.i d = 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.Name ASC; Then it's nice and fast again. The explain shows that it's still a filesort/temp query but it does a indexed table scan instead of an unindexed range scan. I assume that this would need a SearchBuilder mod to force the use of the index related to the ORDER BY clause? 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
