Hi, Has this issue been resolved in RT 3.6.4rc2 by any chance?
Could you please give me an update on the status of the fix? This is becoming a huge problem for us. Thanks, Wojciech Jawor Software Architect Shopzilla, Inc. -----Original Message----- From: Ruslan Zakirov [mailto:[EMAIL PROTECTED] Sent: Thu 26/04/2007 17:34 To: Philip Kime Cc: Jesse Vincent; Todd Chapman; RT Users Subject: Re: [rt-users] Slow ticket search page becoming a problem Philip, please try the following query and send us times and EXPLAIN: SELECT STRAIGHT_JOIN DISTINCT main.* FROM ACL ACL_4, Groups Groups_3, CachedGroupMembers CachedGroupMembers_2, Principals Principals_1, Users main 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; It's the same query but with forced order of joins, I do believe that this is the ideal plan for joins in this situation for all setups. On 4/27/07, Philip Kime <[EMAIL PROTECTED]> wrote: > > > 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: > [snip] > > 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 -- Best regards, Ruslan. _______________________________________________ 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
