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

[Ruslan Zakirov]

I've done more investigation on this query and looks like we have to
do more work to make it better.

On 6/15/07, Wojciech Jawor <wjawor at shopzilla.com> wrote:
> 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:ruslan.zakirov at gmail.com]
> 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 <pkime at shopzilla.com> 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.
>
>
>


-- 
Best regards, Ruslan.