Hi all,
I have recently upgrade a production server to 5.0.2 from 4.2.x
I have moved from mysql to MariaDB.
Whenever I do a search and I sort by any fields the results is pretty snappy, the only exception is the requestor field. Now instead of the usual 1-3 seconds I wait 15-30 seconds.
I think this may be a bug in RT query.
I compared old and new DB queries and what I was able to gather:
OLD system
show full processlist on mysql during the requestor field sort shows :
SELECT 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 ) LEFT JOIN CachedGroupMembers CachedGroupMembers_2 ON ( CachedGroupMembers_2.Disabled = ‘0’ ) AND ( CachedGroupMembers_2.GroupId != CachedGroupMembers_2.MemberId ) AND ( CachedGroupMembers_2.GroupId = Groups_1.id ) LEFT JOIN Users Users_3 ON ( Users_3.id = CachedGroupMembers_2.MemberId ) WHERE (main.IsMerged IS NULL) AND (main.Status != ‘deleted’) AND (main.Type = ‘ticket’) AND (main.Queue = ‘108’ AND ( main.Status = ‘new’ OR main.Status = ‘open’ OR main.Status = ‘review’ OR main.Status = ‘stalled’ OR main.Status = ‘crp’ ) ) GROUP BY main.id ORDER BY MIN(Users_3.EmailAddress) ASC LIMIT 50
NEW system
show full processlist on mariaDB during the requestor field sort shows :
SELECT main.* FROM Tickets main LEFT JOIN Groups Groups_2 ON ( Groups_2.Domain = ‘RT::Ticket-Role’ ) AND ( Groups_2.Name = ‘Requestor’ ) AND ( Groups_2.Instance = main.id ) JOIN Queues Queues_1 ON ( Queues_1.id = main.Queue ) LEFT JOIN CachedGroupMembers CachedGroupMembers_3 ON ( CachedGroupMembers_3.Disabled = ‘0’ ) AND ( CachedGroupMembers_3.GroupId != CachedGroupMembers_3.MemberId ) AND ( CachedGroupMembers_3.GroupId = Groups_2.id ) LEFT JOIN Groups Groups_4 ON ( Groups_4.id = CachedGroupMembers_3.MemberId ) LEFT JOIN Users Users_5 ON ( Users_5.id = CachedGroupMembers_3.MemberId ) WHERE (Groups_4.id IS NULL) AND (main.IsMerged IS NULL) AND (main.Status != ‘deleted’) AND (main.Type = ‘ticket’) AND (main.Queue = ‘124’ AND ( ( Queues_1.Lifecycle = ‘default’ AND main.Status IN (‘crp’, ‘new’, ‘open’, ‘review’, ‘stalled’) ) OR ( Queues_1.Lifecycle = ‘approvals’ AND main.Status IN (‘new’, ‘open’, ‘stalled’) ) ) ) GROUP BY main.id ORDER BY MAX(Users_5.Name) DESC LIMIT 50
That first join is prob the culprit:
FROM Tickets main JOIN Groups Groups_1
vs
FROM Tickets main LEFT JOIN Groups Groups_2
LEFT JOIN will return a bucket load more results and I can’t see why you’d have it, either.
Maybe someone knows more about this issue and has any recommendation ?
/regards
Filip