[rt-users] RT 4.0.8 TicketSQL issue

[David Good]

We have an issue with our RT 4.0.8 instance that we didn't have with
3.8.8 that we just finished migrating from.  Some of our managers like
to search for tickets where the requestor is a 'VIP' user.  They use
fairly straightforward TicketSQL to build saved searches that are then
used for dashboards and RT at a Glance items.  This all worked fine in
RT 3.8.8, but when we upgraded to 4.0.8 the queries take so long to run
that it ends up timing out the webserver, making it very difficult for
users using them in RT at a Glance to login.  Checking the MySQL slow
query log (or just doing a 'show full processlist' while it's running)
shows that fairly straightforward TicketSQL query gets expanded into
ridiculously complex MySQL queries doing multiple joins on the same
tables for the same columns.  Is this a known issue?

Here's an example:

This TicketSQL:

Status = 'open' AND
(
Requestor.EmailAddress = 'bigw...@example.com' OR
Requestor.EmailAddress = 'bigw...@example.com' OR
Requestor.EmailAddress = 'bigw...@example.com' OR
Requestor.EmailAddress = 'bigw...@example.com' OR
Requestor.EmailAddress = 'bigw...@example.com' OR
Requestor.EmailAddress = 'bigw...@example.com' OR
Requestor.EmailAddress = 'bigw...@example.com' OR
Requestor.EmailAddress = 'bigw...@example.com' OR
Requestor.EmailAddress = 'bigw...@example.com' OR
Requestor.EmailAddress = 'bigw...@example.com' OR
Requestor.EmailAddress = 'bigwi...@example.com' OR
Requestor.EmailAddress = 'bigwi...@example.com' OR
Requestor.EmailAddress = 'bigwi...@example.com' OR
Requestor.EmailAddress = 'bigwi...@example.com' OR
Requestor.EmailAddress = 'bigwi...@example.com' OR
Requestor.EmailAddress = 'bigwi...@example.com' OR
Requestor.EmailAddress = 'bigwi...@example.com' OR
Requestor.EmailAddress = 'bigwi...@example.com' OR
Requestor.EmailAddress = 'bigwi...@example.com' OR
Requestor.EmailAddress = 'bigwi...@example.com' OR
Requestor.EmailAddress = 'bigwi...@example.com' OR
Requestor.EmailAddress = 'bigwi...@example.com' OR
Requestor.EmailAddress = 'bigwi...@example.com' OR
Requestor.EmailAddress = 'bigwi...@example.com' OR
Requestor.EmailAddress = 'bigwi...@example.com' OR
Requestor.EmailAddress = 'bigwi...@example.com' OR
Requestor.EmailAddress = 'bigwi...@example.com' OR
Requestor.EmailAddress = 'bigwi...@example.com' OR
Requestor.EmailAddress = 'bigwi...@example.com'
)

Results in this MySQL query:

SELECT Count(DISTINCT main.id)
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 )
       JOIN cachedgroupmembers CachedGroupMembers_54
         ON ( CachedGroupMembers_54.groupid = Groups_1.id )
       JOIN cachedgroupmembers CachedGroupMembers_18
         ON ( CachedGroupMembers_18.groupid = Groups_1.id )
       JOIN cachedgroupmembers CachedGroupMembers_24
         ON ( CachedGroupMembers_24.groupid = Groups_1.id )
       JOIN cachedgroupmembers CachedGroupMembers_16
         ON ( CachedGroupMembers_16.groupid = Groups_1.id )
       JOIN cachedgroupmembers CachedGroupMembers_12
         ON ( CachedGroupMembers_12.groupid = Groups_1.id )
       JOIN cachedgroupmembers CachedGroupMembers_48
         ON ( CachedGroupMembers_48.groupid = Groups_1.id )
       JOIN cachedgroupmembers CachedGroupMembers_2
         ON ( CachedGroupMembers_2.groupid = Groups_1.id )
       JOIN cachedgroupmembers CachedGroupMembers_6
         ON ( CachedGroupMembers_6.groupid = Groups_1.id )
       JOIN cachedgroupmembers CachedGroupMembers_26
         ON ( CachedGroupMembers_26.groupid = Groups_1.id )
       JOIN cachedgroupmembers CachedGroupMembers_22
         ON ( CachedGroupMembers_22.groupid = Groups_1.id )
       JOIN cachedgroupmembers CachedGroupMembers_58
         ON ( CachedGroupMembers_58.groupid = Groups_1.id )
       JOIN cachedgroupmembers CachedGroupMembers_30
         ON ( CachedGroupMembers_30.groupid = Groups_1.id )
       JOIN cachedgroupmembers CachedGroupMembers_44
         ON ( CachedGroupMembers_44.groupid = Groups_1.id )
       JOIN cachedgroupmembers CachedGroupMembers_20
         ON ( CachedGroupMembers_20.groupid = Groups_1.id )
       JOIN cachedgroupmembers CachedGroupMembers_42
         ON ( CachedGroupMembers_42.groupid = Groups_1.id )
       JOIN cachedgroupmembers CachedGroupMembers_8
         ON ( CachedGroupMembers_8.groupid = Groups_1.id )
       JOIN cachedgroupmembers CachedGroupMembers_32
         ON ( CachedGroupMembers_32.groupid = Groups_1.id )
       JOIN cachedgroupmembers CachedGroupMembers_56
         ON ( CachedGroupMembers_56.groupid = Groups_1.id )
       JOIN cachedgroupmembers CachedGroupMembers_38
         ON ( CachedGroupMembers_38.groupid = Groups_1.id )
       JOIN cachedgroupmembers CachedGroupMembers_4
         ON ( CachedGroupMembers_4.groupid = Groups_1.id )
       JOIN cachedgroupmembers CachedGroupMembers_36
         ON ( CachedGroupMembers_36.groupid = Groups_1.id )
       JOIN cachedgroupmembers CachedGroupMembers_14
         ON ( CachedGroupMembers_14.groupid = Groups_1.id )
       JOIN cachedgroupmembers CachedGroupMembers_10
         ON ( CachedGroupMembers_10.groupid = Groups_1.id )
       JOIN cachedgroupmembers CachedGroupMembers_50
         ON ( CachedGroupMembers_50.groupid = Groups_1.id )
       JOIN cachedgroupmembers CachedGroupMembers_40
         ON ( CachedGroupMembers_40.groupid = Groups_1.id )
       JOIN cachedgroupmembers CachedGroupMembers_46
         ON ( CachedGroupMembers_46.groupid = Groups_1.id )
       JOIN cachedgroupmembers CachedGroupMembers_52
         ON ( CachedGroupMembers_52.groupid = Groups_1.id )
       JOIN cachedgroupmembers CachedGroupMembers_28
         ON ( CachedGroupMembers_28.groupid = Groups_1.id )
       JOIN cachedgroupmembers CachedGroupMembers_34
         ON ( CachedGroupMembers_34.groupid = Groups_1.id )
       LEFT JOIN users Users_15
              ON ( Users_15.id = CachedGroupMembers_14.memberid )
       LEFT JOIN users Users_53
              ON ( Users_53.id = CachedGroupMembers_52.memberid )
       LEFT JOIN users Users_7
              ON ( Users_7.id = CachedGroupMembers_6.memberid )
       LEFT JOIN users Users_25
              ON ( Users_25.id = CachedGroupMembers_24.memberid )
       LEFT JOIN users Users_31
              ON ( Users_31.id = CachedGroupMembers_30.memberid )
       LEFT JOIN users Users_51
              ON ( Users_51.id = CachedGroupMembers_50.memberid )
       LEFT JOIN users Users_17
              ON ( Users_17.id = CachedGroupMembers_16.memberid )
       LEFT JOIN users Users_5
              ON ( Users_5.id = CachedGroupMembers_4.memberid )
       LEFT JOIN users Users_49
              ON ( Users_49.id = CachedGroupMembers_48.memberid )
       LEFT JOIN users Users_33
              ON ( Users_33.id = CachedGroupMembers_32.memberid )
       LEFT JOIN users Users_3
              ON ( Users_3.id = CachedGroupMembers_2.memberid )
       LEFT JOIN users Users_47
              ON ( Users_47.id = CachedGroupMembers_46.memberid )
       LEFT JOIN users Users_9
              ON ( Users_9.id = CachedGroupMembers_8.memberid )
       LEFT JOIN users Users_43
              ON ( Users_43.id = CachedGroupMembers_42.memberid )
       LEFT JOIN users Users_27
              ON ( Users_27.id = CachedGroupMembers_26.memberid )
       LEFT JOIN users Users_57
              ON ( Users_57.id = CachedGroupMembers_56.memberid )
       LEFT JOIN users Users_11
              ON ( Users_11.id = CachedGroupMembers_10.memberid )
       LEFT JOIN users Users_35
              ON ( Users_35.id = CachedGroupMembers_34.memberid )
       LEFT JOIN users Users_59
              ON ( Users_59.id = CachedGroupMembers_58.memberid )
       LEFT JOIN users Users_45
              ON ( Users_45.id = CachedGroupMembers_44.memberid )
       LEFT JOIN users Users_29
              ON ( Users_29.id = CachedGroupMembers_28.memberid )
       LEFT JOIN users Users_37
              ON ( Users_37.id = CachedGroupMembers_36.memberid )
       LEFT JOIN users Users_23
              ON ( Users_23.id = CachedGroupMembers_22.memberid )
       LEFT JOIN users Users_41
              ON ( Users_41.id = CachedGroupMembers_40.memberid )
       LEFT JOIN users Users_39
              ON ( Users_39.id = CachedGroupMembers_38.memberid )
       LEFT JOIN users Users_13
              ON ( Users_13.id = CachedGroupMembers_12.memberid )
       LEFT JOIN users Users_19
              ON ( Users_19.id = CachedGroupMembers_18.memberid )
       LEFT JOIN users Users_55
              ON ( Users_55.id = CachedGroupMembers_54.memberid )
       LEFT JOIN users Users_21
              ON ( Users_21.id = CachedGroupMembers_20.memberid )
WHERE  ( CachedGroupMembers_42.disabled = '0' )
       AND ( CachedGroupMembers_30.disabled = '0' )
       AND ( CachedGroupMembers_6.disabled = '0' )
       AND ( CachedGroupMembers_44.disabled = '0' )
       AND ( CachedGroupMembers_52.disabled = '0' )
       AND ( main.type = 'ticket' )
       AND ( CachedGroupMembers_18.disabled = '0' )
       AND ( CachedGroupMembers_12.disabled = '0' )
       AND ( CachedGroupMembers_4.disabled = '0' )
       AND ( CachedGroupMembers_48.disabled = '0' )
       AND ( CachedGroupMembers_16.disabled = '0' )
       AND ( main.status = 'open'
             AND ( ( Users_3.emailaddress = 'bigw...@example.com' )
                    OR ( Users_5.emailaddress = 'bigw...@example.com' )
                    OR ( Users_7.emailaddress = 'bigw...@example.com' )
                    OR ( Users_9.emailaddress = 'bigw...@example.com' )
                    OR ( Users_11.emailaddress = 'bigw...@example.com' )
                    OR ( Users_13.emailaddress = 'bigw...@example.com' )
                    OR ( Users_15.emailaddress = 'bigw...@example.com' )
                    OR ( Users_17.emailaddress = 'bigw...@example.com' )
                    OR ( Users_19.emailaddress = 'bigw...@example.com' )
                    OR ( Users_21.emailaddress = 'bigw...@example.com' )
                    OR ( Users_23.emailaddress = 'bigwi...@example.com' )
                    OR ( Users_25.emailaddress = 'bigwi...@example.com' )
                    OR ( Users_27.emailaddress = 'bigwi...@example.com' )
                    OR ( Users_29.emailaddress = 'bigwi...@example.com' )
                    OR ( Users_31.emailaddress = 'bigwi...@example.com' )
                    OR ( Users_33.emailaddress = 'bigwi...@example.com' )
                    OR ( Users_35.emailaddress = 'bigwi...@example.com' )
                    OR ( Users_37.emailaddress = 'bigwi...@example.com' )
                    OR ( Users_39.emailaddress = 'bigwi...@example.com' )
                    OR ( Users_41.emailaddress = 'bigwi...@example.com' )
                    OR ( Users_43.emailaddress = 'bigwi...@example.com' )
                    OR ( Users_45.emailaddress = 'bigwi...@example.com' )
                    OR ( Users_47.emailaddress = 'bigwi...@example.com' )
                    OR ( Users_49.emailaddress = 'bigwi...@example.com' )
                    OR ( Users_51.emailaddress = 'bigwi...@example.com' )
                    OR ( Users_53.emailaddress = 'bigwi...@example.com' )
                    OR ( Users_55.emailaddress = 'bigwi...@example.com' )
                    OR ( Users_57.emailaddress = 'bigwi...@example.com' )
                    OR ( Users_59.emailaddress = 'bigwi...@example.com'
) ) )
       AND ( CachedGroupMembers_34.disabled = '0' )
       AND ( CachedGroupMembers_24.disabled = '0' )
       AND ( CachedGroupMembers_46.disabled = '0' )
       AND ( CachedGroupMembers_8.disabled = '0' )
       AND ( CachedGroupMembers_22.disabled = '0' )
       AND ( CachedGroupMembers_28.disabled = '0' )
       AND ( CachedGroupMembers_56.disabled = '0' )
       AND ( CachedGroupMembers_54.disabled = '0' )
       AND ( CachedGroupMembers_2.disabled = '0' )
       AND ( CachedGroupMembers_14.disabled = '0' )
       AND ( CachedGroupMembers_36.disabled = '0' )
       AND ( main.effectiveid = main.id )
       AND ( CachedGroupMembers_50.disabled = '0' )
       AND ( CachedGroupMembers_38.disabled = '0' )
       AND ( CachedGroupMembers_32.disabled = '0' )
       AND ( CachedGroupMembers_20.disabled = '0' )
       AND ( CachedGroupMembers_10.disabled = '0' )
       AND ( CachedGroupMembers_58.disabled = '0' )
       AND ( main.status != 'deleted' )
       AND ( CachedGroupMembers_26.disabled = '0' )
       AND ( CachedGroupMembers_40.disabled = '0' )


-- 
Help improve RT by taking our user survey: 
https://www.surveymonkey.com/s/N23JW9T