Hi,
We have problems with the search tickets function.
I checked the mariadb log and I found the query causing problems:
SELECT DISTINCT main.*
FROM Tickets main
JOIN Groups Groups_1 ON ( Groups_1.Domain = ‘RT::Ticket-Role’ ) AND ( Groups_1.Name = ‘Requestor’ ) AND ( Groups_1.Instance = main.id )
JOIN CachedGroupMembers CachedGroupMembers_10 ON ( CachedGroupMembers_10.Disabled = ‘0’ ) AND ( CachedGroupMembers_10.GroupId = Groups_1.id )
JOIN CachedGroupMembers CachedGroupMembers_12 ON ( CachedGroupMembers_12.Disabled = ‘0’ ) AND ( CachedGroupMembers_12.GroupId = Groups_1.id )
JOIN CachedGroupMembers CachedGroupMembers_14 ON ( CachedGroupMembers_14.Disabled = ‘0’ ) AND ( CachedGroupMembers_14.GroupId = Groups_1.id )
JOIN CachedGroupMembers CachedGroupMembers_16 ON ( CachedGroupMembers_16.Disabled = ‘0’ ) AND ( CachedGroupMembers_16.GroupId = Groups_1.id )
JOIN CachedGroupMembers CachedGroupMembers_18 ON ( CachedGroupMembers_18.Disabled = ‘0’ ) AND ( CachedGroupMembers_18.GroupId = Groups_1.id )
JOIN CachedGroupMembers CachedGroupMembers_2 ON ( CachedGroupMembers_2.Disabled = ‘0’ ) AND ( CachedGroupMembers_2.GroupId = Groups_1.id )
JOIN CachedGroupMembers CachedGroupMembers_20 ON ( CachedGroupMembers_20.Disabled = ‘0’ ) AND ( CachedGroupMembers_20.GroupId = Groups_1.id )
JOIN CachedGroupMembers CachedGroupMembers_22 ON ( CachedGroupMembers_22.Disabled = ‘0’ ) AND ( CachedGroupMembers_22.GroupId = Groups_1.id )
JOIN CachedGroupMembers CachedGroupMembers_24 ON ( CachedGroupMembers_24.Disabled = ‘0’ ) AND ( CachedGroupMembers_24.GroupId = Groups_1.id )
JOIN CachedGroupMembers CachedGroupMembers_26 ON ( CachedGroupMembers_26.Disabled = ‘0’ ) AND ( CachedGroupMembers_26.GroupId = Groups_1.id )
JOIN CachedGroupMembers CachedGroupMembers_4 ON ( CachedGroupMembers_4.Disabled = ‘0’ ) AND ( CachedGroupMembers_4.GroupId = Groups_1.id )
JOIN CachedGroupMembers CachedGroupMembers_6 ON ( CachedGroupMembers_6.Disabled = ‘0’ ) AND ( CachedGroupMembers_6.GroupId = Groups_1.id )
JOIN CachedGroupMembers CachedGroupMembers_8 ON ( CachedGroupMembers_8.Disabled = ‘0’ ) AND ( CachedGroupMembers_8.GroupId = Groups_1.id )
LEFT JOIN Users Users_11 ON ( Users_11.id = CachedGroupMembers_10.MemberId )
LEFT JOIN Users Users_13 ON ( Users_13.id = CachedGroupMembers_12.MemberId )
LEFT JOIN Users Users_15 ON ( Users_15.id = CachedGroupMembers_14.MemberId )
LEFT JOIN Users Users_17 ON ( Users_17.id = CachedGroupMembers_16.MemberId )
LEFT JOIN Users Users_19 ON ( Users_19.id = CachedGroupMembers_18.MemberId )
LEFT JOIN Users Users_21 ON ( Users_21.id = CachedGroupMembers_20.MemberId )
LEFT JOIN Users Users_23 ON ( Users_23.id = CachedGroupMembers_22.MemberId )
LEFT JOIN Users Users_25 ON ( Users_25.id = CachedGroupMembers_24.MemberId )
LEFT JOIN Users Users_27 ON ( Users_27.id = CachedGroupMembers_26.MemberId )
LEFT JOIN Users Users_3 ON ( Users_3.id = CachedGroupMembers_2.MemberId )
LEFT JOIN Users Users_5 ON ( Users_5.id = CachedGroupMembers_4.MemberId )
LEFT JOIN Users Users_7 ON ( Users_7.id = CachedGroupMembers_6.MemberId )
LEFT JOIN Users Users_9 ON ( Users_9.id = CachedGroupMembers_8.MemberId )
WHERE (main.IsMerged IS NULL)
AND (main.Status != ‘deleted’)
AND (main.Type = ‘ticket’)
AND ( ( Users_3.EmailAddress LIKE ‘%email1@email.com%’ )
OR ( Users_5.EmailAddress LIKE ‘%email2@email.com%’ )
OR ( Users_7.EmailAddress LIKE ‘%email3@email.com%’ )
OR ( Users_9.EmailAddress LIKE ‘%email4@email.com%’ )
OR ( Users_11.EmailAddress LIKE ‘%email5@email.com%’ )
OR ( Users_13.EmailAddress LIKE ‘%email6@email.com%’ )
OR ( Users_15.EmailAddress LIKE ‘%email7@email.com%’ )
OR ( Users_17.EmailAddress LIKE ‘%email8@email.com%’ )
OR ( Users_19.EmailAddress LIKE ‘%email9@email.com%’ )
OR ( Users_21.EmailAddress LIKE ‘%email10@email.com%’ )
OR ( Users_23.EmailAddress LIKE ‘%email11@email.com%’ )
OR ( Users_25.EmailAddress LIKE ‘%email12@email.com%’ )
OR ( Users_27.EmailAddress LIKE ‘%email13@email.com%’ ) );
The mariadb database executes the query but it takes so long that RT stop working.
I run the query with only 7 users and it took 15 minutes. The CachedGroupMembers table contains 120000 entries and the Users table 1300…
How can we fix this? I cannot say to the users “please do not put more than 6 requestors in your search”…
Regards,
Alberto