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

[Dirk Pape]

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

 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.