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

[Jesse Vincent]

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 ;)



> 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
>