[rt-devel] Slow Queries part3, Groups

[Robert Spier]

> yeh, my real problem is I have almost 700,000 tickets and 150,000 users :)
> so that 1 second blows out a bit :)

Oy.  

> > Do you know what is triggering the query?
> 
> Yep, its when you click "reply" on a ticket, its trying to find
> people who have the right to own the ticket (so it also happens when
> you click on "People"

This was slow in RT2 as well.  I solved it (er.. "worked around") by
making the "Owner" box a text field instead of a select box.

> > I suspect the real problem here is MySQL's optimizer is falling down.
> > Which means we may have to take an alternate track.  Anyone want to
> > try MySQL 4.1?  Or (Jesse?) maybe split the OR's up into a few faster
> > queries?  Another thought is to replace some of the text strings with
> > lookup numbers.. but that'll slow other things down.
> 
> Yep, there are a few places where mysql will refuse to use indexes,
> I'd say probably in this case, its deciding that the index just
> doesn't cut the rows down enough and a sequential search will be
> faster.

Right.  Thats what the documentation says.  But even when I force
those indexes, it still doesn't cut the rows down enough.  (Although
if it maintains the 50% margin on your system.. that might help.)

> I think probably a couple of faster queries will be better, unless
> a mysql guru can optimise the current one?

Refactoring this gets a little funny, because it may have to touch a
lot of code.  The ugly query is built in Groups_Overlay.pm:WithRight
-- and sets the Groups iterator to dtrt.  (And called from
Users_Overlay.pm:WhoHaveRight).

(Jesse-	
    1- Why doesn't this use a recursive search and therefore use
    CachedGroupMembers?
    2- IncludeSystemRights doesn't seem to do anything anymore.
    Should it be excised?
)

> > Ok.  Enough time with this for tonight.
> Hehe, thanks for you efforts, its much appreciated.

My pleasure.  While your database _is_ huge, we do want to try and nip
these issues.

-R