RT 4.0.8 TicketSQL issue

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 = ‘bigwig0@example.com’ OR
Requestor.EmailAddress = ‘bigwig1@example.com’ OR
Requestor.EmailAddress = ‘bigwig2@example.com’ OR
Requestor.EmailAddress = ‘bigwig3@example.com’ OR
Requestor.EmailAddress = ‘bigwig4@example.com’ OR
Requestor.EmailAddress = ‘bigwig5@example.com’ OR
Requestor.EmailAddress = ‘bigwig6@example.com’ OR
Requestor.EmailAddress = ‘bigwig7@example.com’ OR
Requestor.EmailAddress = ‘bigwig8@example.com’ OR
Requestor.EmailAddress = ‘bigwig9@example.com’ OR
Requestor.EmailAddress = ‘bigwig10@example.com’ OR
Requestor.EmailAddress = ‘bigwig11@example.com’ OR
Requestor.EmailAddress = ‘bigwig12@example.com’ OR
Requestor.EmailAddress = ‘bigwig13@example.com’ OR
Requestor.EmailAddress = ‘bigwig14@example.com’ OR
Requestor.EmailAddress = ‘bigwig15@example.com’ OR
Requestor.EmailAddress = ‘bigwig16@example.com’ OR
Requestor.EmailAddress = ‘bigwig17@example.com’ OR
Requestor.EmailAddress = ‘bigwig18@example.com’ OR
Requestor.EmailAddress = ‘bigwig19@example.com’ OR
Requestor.EmailAddress = ‘bigwig20@example.com’ OR
Requestor.EmailAddress = ‘bigwig21@example.com’ OR
Requestor.EmailAddress = ‘bigwig22@example.com’ OR
Requestor.EmailAddress = ‘bigwig23@example.com’ OR
Requestor.EmailAddress = ‘bigwig24@example.com’ OR
Requestor.EmailAddress = ‘bigwig25@example.com’ OR
Requestor.EmailAddress = ‘bigwig26@example.com’ OR
Requestor.EmailAddress = ‘bigwig27@example.com’ OR
Requestor.EmailAddress = ‘bigwig28@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 = ‘bigwig0@example.com’ )
OR ( Users_5.emailaddress = ‘bigwig1@example.com’ )
OR ( Users_7.emailaddress = ‘bigwig2@example.com’ )
OR ( Users_9.emailaddress = ‘bigwig3@example.com’ )
OR ( Users_11.emailaddress = ‘bigwig4@example.com’ )
OR ( Users_13.emailaddress = ‘bigwig5@example.com’ )
OR ( Users_15.emailaddress = ‘bigwig6@example.com’ )
OR ( Users_17.emailaddress = ‘bigwig7@example.com’ )
OR ( Users_19.emailaddress = ‘bigwig8@example.com’ )
OR ( Users_21.emailaddress = ‘bigwig9@example.com’ )
OR ( Users_23.emailaddress = ‘bigwig10@example.com’ )
OR ( Users_25.emailaddress = ‘bigwig11@example.com’ )
OR ( Users_27.emailaddress = ‘bigwig12@example.com’ )
OR ( Users_29.emailaddress = ‘bigwig13@example.com’ )
OR ( Users_31.emailaddress = ‘bigwig14@example.com’ )
OR ( Users_33.emailaddress = ‘bigwig15@example.com’ )
OR ( Users_35.emailaddress = ‘bigwig16@example.com’ )
OR ( Users_37.emailaddress = ‘bigwig17@example.com’ )
OR ( Users_39.emailaddress = ‘bigwig18@example.com’ )
OR ( Users_41.emailaddress = ‘bigwig19@example.com’ )
OR ( Users_43.emailaddress = ‘bigwig20@example.com’ )
OR ( Users_45.emailaddress = ‘bigwig21@example.com’ )
OR ( Users_47.emailaddress = ‘bigwig22@example.com’ )
OR ( Users_49.emailaddress = ‘bigwig23@example.com’ )
OR ( Users_51.emailaddress = ‘bigwig24@example.com’ )
OR ( Users_53.emailaddress = ‘bigwig25@example.com’ )
OR ( Users_55.emailaddress = ‘bigwig26@example.com’ )
OR ( Users_57.emailaddress = ‘bigwig27@example.com’ )
OR ( Users_59.emailaddress = ‘bigwig28@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’ )

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?

Open bug here: Login

Another RT user wrote a patch which is MySQL-specific, but it’s not a
clean solution (code-wise) and has some problems which make it not
something we can just ship as-is (see the ticket).

It’s a straightforward query, but also seems a tad ridiculous to be
listing so many individual addresses. As a more maintainable solution,
and a workaround to the bug, try setting the Organization of every one
of those big wigs to “VIP” or “Executive” or similar. Then you can
write a much nicer query like this:

Status = 'open' and Requestor.Organization = 'VIP'

This also means all you need to do when a big wig cashes out or a new
one comes in is to adjust their Organization, rather than potentially
edit a whole bunch of saved searches.

If you’re already using Organization, you can use another user field
instead (some are searchable by default, others need a couple config
tweaks).