[rt-users] RT 4.0.8 TicketSQL issue
David Good
dgood at willingminds.com
Tue Feb 5 18:35:31 EST 2013
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 at example.com' OR
Requestor.EmailAddress = 'bigwig1 at example.com' OR
Requestor.EmailAddress = 'bigwig2 at example.com' OR
Requestor.EmailAddress = 'bigwig3 at example.com' OR
Requestor.EmailAddress = 'bigwig4 at example.com' OR
Requestor.EmailAddress = 'bigwig5 at example.com' OR
Requestor.EmailAddress = 'bigwig6 at example.com' OR
Requestor.EmailAddress = 'bigwig7 at example.com' OR
Requestor.EmailAddress = 'bigwig8 at example.com' OR
Requestor.EmailAddress = 'bigwig9 at example.com' OR
Requestor.EmailAddress = 'bigwig10 at example.com' OR
Requestor.EmailAddress = 'bigwig11 at example.com' OR
Requestor.EmailAddress = 'bigwig12 at example.com' OR
Requestor.EmailAddress = 'bigwig13 at example.com' OR
Requestor.EmailAddress = 'bigwig14 at example.com' OR
Requestor.EmailAddress = 'bigwig15 at example.com' OR
Requestor.EmailAddress = 'bigwig16 at example.com' OR
Requestor.EmailAddress = 'bigwig17 at example.com' OR
Requestor.EmailAddress = 'bigwig18 at example.com' OR
Requestor.EmailAddress = 'bigwig19 at example.com' OR
Requestor.EmailAddress = 'bigwig20 at example.com' OR
Requestor.EmailAddress = 'bigwig21 at example.com' OR
Requestor.EmailAddress = 'bigwig22 at example.com' OR
Requestor.EmailAddress = 'bigwig23 at example.com' OR
Requestor.EmailAddress = 'bigwig24 at example.com' OR
Requestor.EmailAddress = 'bigwig25 at example.com' OR
Requestor.EmailAddress = 'bigwig26 at example.com' OR
Requestor.EmailAddress = 'bigwig27 at example.com' OR
Requestor.EmailAddress = 'bigwig28 at 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 at example.com' )
OR ( Users_5.emailaddress = 'bigwig1 at example.com' )
OR ( Users_7.emailaddress = 'bigwig2 at example.com' )
OR ( Users_9.emailaddress = 'bigwig3 at example.com' )
OR ( Users_11.emailaddress = 'bigwig4 at example.com' )
OR ( Users_13.emailaddress = 'bigwig5 at example.com' )
OR ( Users_15.emailaddress = 'bigwig6 at example.com' )
OR ( Users_17.emailaddress = 'bigwig7 at example.com' )
OR ( Users_19.emailaddress = 'bigwig8 at example.com' )
OR ( Users_21.emailaddress = 'bigwig9 at example.com' )
OR ( Users_23.emailaddress = 'bigwig10 at example.com' )
OR ( Users_25.emailaddress = 'bigwig11 at example.com' )
OR ( Users_27.emailaddress = 'bigwig12 at example.com' )
OR ( Users_29.emailaddress = 'bigwig13 at example.com' )
OR ( Users_31.emailaddress = 'bigwig14 at example.com' )
OR ( Users_33.emailaddress = 'bigwig15 at example.com' )
OR ( Users_35.emailaddress = 'bigwig16 at example.com' )
OR ( Users_37.emailaddress = 'bigwig17 at example.com' )
OR ( Users_39.emailaddress = 'bigwig18 at example.com' )
OR ( Users_41.emailaddress = 'bigwig19 at example.com' )
OR ( Users_43.emailaddress = 'bigwig20 at example.com' )
OR ( Users_45.emailaddress = 'bigwig21 at example.com' )
OR ( Users_47.emailaddress = 'bigwig22 at example.com' )
OR ( Users_49.emailaddress = 'bigwig23 at example.com' )
OR ( Users_51.emailaddress = 'bigwig24 at example.com' )
OR ( Users_53.emailaddress = 'bigwig25 at example.com' )
OR ( Users_55.emailaddress = 'bigwig26 at example.com' )
OR ( Users_57.emailaddress = 'bigwig27 at example.com' )
OR ( Users_59.emailaddress = 'bigwig28 at 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' )
More information about the rt-users
mailing list