[Rt-devel] 3.6.4rc1 performance regression on a fairly complex search (CROSS JOIN???)

[Ruslan Zakirov]

On 5/29/07, Jesse Vincent <jesse at bestpractical.com> wrote:
>
> On May 29, 2007, at 3:36 PM, Dirk Pape wrote:
>
> > Halle Jesse, hello Ruslan,
> >
> > the following ticket search query (captured from the advanced tab)
> > leads to very different Searchbuilder compilations in 3.6.3 an
> > 3.6.4rc1 (captured from mysql-slow.log):
>
> So, I think the CROSS Joins are a red herring. This looks like a
> bundling change. The issue is the multiple joins on Users. Ruz: What
> got touched there? (And yes, this is enough to cause an rc2 ;)
Yes, I touched that code and I do believe it's better than code we
had. Added a lot of tests for searches by watchers, there were a lot
of bugs in old code. I have an idea how to make things better, but
that will need some time to implement.

For those who are not familiar with SQL:
http://en.wikipedia.org/wiki/Join_(SQL)#Cross_join

>
>
>
> > Queue = 'trash' AND  (  Requestor.EmailAddress LIKE 'staff@' OR
> > Requestor.EmailAddress LIKE 'math.fu' OR Requestor.EmailAddress LIKE
> > 'inf.fu' OR Requestor.EmailAddress LIKE 'mi.fu' OR
> > Requestor.EmailAddress LIKE 'cedis.fu' OR Requestor.EmailAddress LIKE
> > 'zuv.fu' OR Requestor.EmailAddress LIKE 'pcpool.mi' ) AND  (  Status =
> > 'new' OR Status = 'open' )
> >
> > is very straight forward and reasonably fast with 3.6.3:
> >
> > # Query_time: 5  Lock_time: 0  Rows_sent: 1  Rows_examined: 259307
> > SELECT COUNT(DISTINCT main.id) FROM Tickets main JOIN Groups
> > Groups_1  ON ( Groups_1.Instance = main.id ) JOIN
> > CachedGroupMembers CachedGroupMembers_2 ON
> > ( CachedGroupMembers_2.GroupId != CachedGroupMembers_2.MemberId )
> > AND ( CachedGroupMembers_2.GroupId = Groups_1.id ) JOIN Users
> > Users_3  ON ( Users_3.id = CachedGroupMembers_2.MemberId )  WHERE
> > (Groups_1.Domain = 'RT::Ticket-Role') AND (main.Status !=
> > 'deleted') AND (Groups_1.Type = 'Requestor') AND (main.Queue = '57'
> > AND  (  ( Users_3.EmailAddress LIKE '%staff@%' OR
> > Users_3.EmailAddress LIKE '%math.fu%' OR Users_3.EmailAddress LIKE
> > '%inf.fu%' OR Users_3.EmailAddress LIKE '%mi.fu%' OR
> > Users_3.EmailAddress LIKE '%cedis.fu%' OR Users_3.EmailAddress LIKE
> > '%zuv.fu%' OR Users_3.EmailAddress LIKE '%pcpool.mi%' )  )  AND
> > ( main.Status = 'new' OR main.Status = 'open' ) ) AND (main.Type =
> > 'ticket') AND (main.EffectiveId = main.id);
> >
> > but with 3.6.4rc1 introduces some CROSS JOINs (I never ever heard
> > of anything like this), which seems not to be a good thing:
> >
> > # Query_time: 373  Lock_time: 0  Rows_sent: 0  Rows_examined: 54382391
> > SELECT COUNT(DISTINCT main.id) FROM Tickets main CROSS JOIN Users
> > Users_3 CROSS JOIN Users Users_5 CROSS JOIN Users Users_7 CROSS
> > JOIN Users Users_9 CROSS JOIN Users Users_11 CROSS JOIN Users
> > Users_13 CROSS JOIN Users Users_15 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_14  ON
> > ( CachedGroupMembers_14.GroupId = Groups_1.id ) AND
> > ( CachedGroupMembers_14.MemberId = Users_15.id ) LEFT JOIN
> > CachedGroupMembers CachedGroupMembers_10  ON ( CachedGroupMembers_10.M
> > emberId = Users_11.id ) AND ( CachedGroupMembers_10.GroupId =
> > Groups_1.id ) LEFT JOIN CachedGroupMembers CachedGroupMembers_8  ON
> > ( CachedGroupMembers_8.GroupId = Groups_1.id ) AND
> > ( CachedGroupMembers_8.MemberId = Users_9.id ) LEFT JOIN
> > CachedGroupMembers CachedGroupMembers_12  ON
> > ( CachedGroupMembers_12.MemberId = Users_13.id ) AND
> > ( CachedGroupMembers_12.GroupId = Groups_1.id ) LEFT JOIN
> > CachedGroupMembers CachedGroupMembers_2  ON
> > ( CachedGroupMembers_2.MemberId = Users_3.id ) AND
> > ( CachedGroupMembers_2.GroupId = Groups_1.id ) LEFT JOIN
> > CachedGroupMembers CachedGroupMembers_6  ON
> > ( CachedGroupMembers_6.MemberId = Users_7.id ) AND
> > ( CachedGroupMembers_6.GroupId = Groups_1.id ) LEFT JOIN
> > CachedGroupMembers CachedGroupMembers_4  ON
> > ( CachedGroupMembers_4.MemberId = Users_5.id ) AND
> > ( CachedGroupMembers_4.GroupId = Groups_1.id )
> > WHERE (main.Status != 'deleted') AND (main.Queue = '57' AND
> > (  ( Users_3.EmailAddress LIKE '%staff@%' AND
> > CachedGroupMembers_2.id IS NOT NULL )  OR  ( Users_5.EmailAddress
> > LIKE '%math.fu%' AND CachedGroupMembers_4.id IS NOT NULL )  OR
> > ( Users_7.EmailAddress LIKE '%inf.fu%' AND CachedGroupMembers_6.id
> > IS NOT NULL )  OR  ( Users_9.EmailAddress LIKE '%mi.fu%' AND
> > CachedGroupMembers_8.id IS NOT NULL )  OR  ( Users_11.EmailAddress
> > LIKE '%cedis.fu%' AND CachedGroupMembers_10.id IS NOT NULL )  OR
> > ( Users_13.EmailAddress LIKE '%zuv.fu%' AND
> > CachedGroupMembers_12.id IS NOT NULL )  OR  ( Users_15.EmailAddress
> > LIKE '%pcpool.mi%' AND CachedGroupMembers_14.id IS NOT NULL )  )
> > AND  ( main.Status = 'new' OR main.Status =
> > 'open' ) ) AND (main.Type = 'ticket') AND (main.EffectiveId =
> > main.id);
> >
> > Sorry for line breaks. I hope you can reproduce and fix it for 3.6.4.
> >
> > Regards, Dirk.
> > _______________________________________________
> > List info: http://lists.bestpractical.com/cgi-bin/mailman/listinfo/
> > rt-devel
> >
>
> _______________________________________________
> List info: http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-devel
>


-- 
Best regards, Ruslan.