[rt-users] Re: [PERFORMANCE][PATCH] Searches by watchers with OR aggregator

[Ruslan Zakirov]

On 7/26/07, Brian Kerr <kerrboy at gmail.com> wrote:
> On 7/25/07, Ruslan Zakirov <ruz at bestpractical.com> wrote:
> >   Hi, guys.
> >
> > Thank you for the feedback. Here is patch that should address
> > performance problems with queries like:
> > "Owner = X OR Requestor = Y"
> > "Requestor = Y OR Status = X"
> > and other queries where positive search conditions (=, like ...) on
> > Requestor, Cc, AdminCc or Watchers are joined with other conditions
> > using OR. It doesn't apply to queries with all ANDs, as well it's not
> > about searches by links, dates or something else. It's about watchers
> > and ORs.
>
> Hi Ruslan,
>
> Thanks for your help.  The patch fixed the query in question.  There
> is another query behind it that is extremely slow under MySQL 4.1.22,
> apparently.  Under MySQL 4.1.18 it takes 3 seconds - on MySQL 4.1.22
> it takes 3minutes.  Yikes.
Can you give me EXPLAINs? Wonder if you can give explain of the query
for both versions of mysql?

Keneth, Robert or other RT users, could you please try to reproduce
results Brian sees.

>
> Seems like a pretty huge difference for any possible query optimizer
> changes from 4.1.18 -> 4.1.21.
When I'm talking about mysql query optimizer, I'm talking not about
optimizations mysql uses during execution of a query, but about the
mysql query planner which defines order of operations, which indexes
to use and so on. When you run EXPLAIN SELECT... you're executing the
planner and get a plan mysql will use to execute the query. Even minor
changes in a plan can kill performance.


Ok, we have another variant of the query. Remember that third query we
tested before with COUNT(...)? Try the following query:
SELECT SQL_NO_CACHE DISTINCT main.*
FROM Tickets main
    JOIN Groups Groups_1
        ON  Groups_1.Domain = 'RT::Ticket-Role'
        AND Groups_1.Type = 'Requestor'
        AND Groups_1.Instance = main.id
    LEFT JOIN CachedGroupMembers CachedGroupMembers_2
        ON CachedGroupMembers_2.GroupId = Groups_1.id
    LEFT JOIN Users Users_3
        ON CachedGroupMembers_2.MemberId = Users_3.id
WHERE
    main.Status != 'deleted'
    AND main.Type = 'ticket'
    AND main.EffectiveId = main.id
    AND ( main.Owner = '58936' OR Users_3.EmailAddress LIKE '%tom%' )
ORDER BY main.id ASC LIMIT 50

May be we should switch to this variant even if counting ticket with
it is slower.




>
> Looks like I might need to downgrade mysql.
>
> SELECT DISTINCT main.* FROM Tickets main CROSS JOIN Users Users_3 JOIN
> Groups Groups_1  ON ( Groups_1.Domain = 'RT::Ticket-Role' ) AND (
> Groups_1.Type = 'Requestor' ) AND ( Groups_1.Instance = main.id ) LEFT
> JOIN CachedGroupMembers CachedGroupMembers_2  ON (
> CachedGroupMembers_2.MemberId = Users_3.id ) AND (
> CachedGroupMembers_2.GroupId = Groups_1.id )  WHERE
> (Users_3.EmailAddress LIKE '%tom%') AND (main.Status != 'deleted') AND
> (main.Owner = '58936' OR  ( CachedGroupMembers_2.id IS NOT NULL ) )
> AND (main.Type = 'ticket') AND (main.EffectiveId = main.id)  ORDER BY
> main.id ASC  LIMIT 50
>
> 50 rows in set (3 min 2.12 sec)
>


-- 
Best regards, Ruslan.