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

[Ruslan Zakirov]

Hi, guys.

The explain is bad :( I mean gives us nothing good. I think we need an
index on the Groups table to make it better. Run the following query
to create an index. This may take some time, but I'm not sure how
much, depends on size of the table.

CREATE INDEX RUZ_G1 ON Groups(Type, Domain);

And then redo explain for the query. If mysql will start search from
Users table then I have no ideas anymore and we must file a bug into
mysql's tracker.

On 6/27/07, Philip Kime <pkime at shopzilla.com> wrote:
> mysql> EXPLAIN SELECT DISTINCT main.* FROM    ACL ACL_4,    Groups Groups_3,
> CachedGroupMembers CachedGroupMembers_2,    Principals Principals_1,    Users ma
> in WHERE ((ACL_4.PrincipalType = Groups_3.Type))    AND ((ACL_4.RightName = 'Own
> Ticket'))    AND ((CachedGroupMembers_2.MemberId = Principals_1.id))    AND ((Gr
> oups_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')
>  ORDER BY main.RealName ASC\G
[snip explain]

-- 
Best regards, Ruslan.