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

[Brian Kerr]

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.

Seems like a pretty huge difference for any possible query optimizer
changes from 4.1.18 -> 4.1.21.

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)