Hi, raising here as the RT Users is not the right forum for it RT becomes unusably slow without Global ShowTicket privilege
TL;DR:
New generated SQL query on Dashboard load leads to unacceptable execution time → unusable UI. For example: Logging in and viewing default dashboard might take several minutes.
From discussion in the user forum above, it sounds like it’s restricted to MySQL / MariaDB systems.
We didn’t notice it for a long time because we have very few external users, however it definitely functioned correctly in earlier versions of RT. I can’t tell you definitively when it changed (either 3.8 → 4.x series or 4.x → 5 but I’m not sure which).
Detail:
U
SELECT COUNT(DISTINCT main.id) FROM Tickets main LEFT JOIN Groups Groups_1 ON ( Groups_1.Domain = 'RT::Ticket-Role' ) AND ( Groups_1.Instance = main.id ) LEFT JOIN CachedGroupMembers CachedGroupMembers_2 ON ( CachedGroupMembers_2.Disabled = '0' ) AND ( CachedGroupMembers_2.MemberId IN ('1567059', '498436') ) AND ( CachedGroupMembers_2.GroupId = Groups_1.id ) WHERE ( ( main.Queue IN ('3', '111') OR ( main.Owner = '1567059' AND main.Queue IN ('2') ) OR ( CachedGroupMembers_2.MemberId IS NOT NULL AND Groups_1.Name = 'Requestor' ) OR ( CachedGroupMembers_2.MemberId IS NOT NULL AND Groups_1.Name = 'AdminCc' AND main.Queue IN ('2') ) ) ) AND (main.IsMerged IS NULL) AND (main.Status != 'deleted') AND (main.Type = 'ticket') AND (main.Owner = '10' AND main.Status IN ('new', 'open'))
explain SELECT COUNT(DISTINCT main.id) FROM Tickets main LEFT JOIN Groups Groups_2 ON ( Groups_2.Domain = 'RT::Ticket-Role' ) 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.MemberId IN ('1567059', '498436') ) AND ( CachedGroupMembers_3.GroupId = Groups_2.id ) WHERE ( ( main.Queue IN ('3', '111') OR ( CachedGroupMembers_3.MemberId IS NOT NULL AND Groups_2.Name = 'AdminCc' AND main.Queue IN ('2') ) OR ( main.Owner = '1567059' AND main.Queue IN ('2') ) OR ( CachedGroupMembers_3.MemberId IS NOT NULL AND Groups_2.Name = 'Requestor' ) ) ) AND (main.IsMerged IS NULL) AND (main.Status != 'deleted') AND (main.Type = 'ticket') AND (main.Queue = '111' AND ( ( Queues_1.Lifecycle = 'approvals' AND main.Status IN ('new', 'open', 'stalled') ) OR ( Queues_1.Lifecycle = 'default' AND main.Status IN ('crp', 'new', 'open', 'review', 'stalled') ) ) )
SELECT DISTINCT main.* FROM Tickets main LEFT JOIN Groups Groups_2 ON ( Groups_2.Domain = 'RT::Ticket-Role' ) 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.MemberId IN ('1567059', '498436') ) AND ( CachedGroupMembers_3.GroupId = Groups_2.id ) WHERE ( ( main.Queue IN ('3', '111') OR ( CachedGroupMembers_3.MemberId IS NOT NULL AND Groups_2.Name = 'AdminCc' AND main.Queue IN ('2') ) OR ( main.Owner = '1567059' AND main.Queue IN ('2') ) OR ( CachedGroupMembers_3.MemberId IS NOT NULL AND Groups_2.Name = 'Requestor' ) ) ) AND (main.IsMerged IS NULL) AND (main.Status != 'deleted') AND (main.Type = 'ticket') AND (main.Queue = '111' 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') ) ) ) ORDER BY main.id ASC LIMIT 50
SELECT COUNT(DISTINCT main.id) FROM Tickets main LEFT JOIN Groups Groups_1 ON ( Groups_1.Domain = 'RT::Ticket-Role' ) AND ( Groups_1.Instance = main.id ) LEFT JOIN CachedGroupMembers CachedGroupMembers_2 ON ( CachedGroupMembers_2.Disabled = '0' ) AND ( CachedGroupMembers_2.MemberId IN ('1567059', '498436') ) AND ( CachedGroupMembers_2.GroupId = Groups_1.id ) WHERE ( ( main.Queue IN ('3', '111') OR ( main.Owner = '1567059' AND main.Queue IN ('2') ) OR ( CachedGroupMembers_2.MemberId IS NOT NULL AND Groups_1.Name = 'Requestor' ) OR ( CachedGroupMembers_2.MemberId IS NOT NULL AND Groups_1.Name = 'AdminCc' AND main.Queue IN ('2') ) ) ) AND (main.IsMerged IS NULL) AND (main.Status != 'deleted') AND (main.Type = 'ticket') AND (main.Owner = '10' AND main.Status IN ('new', 'open'));
These result in 2x query execution plans:
First:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE main range Tickets1,Tickets2,Status,Type Tickets1 263 NULL 248 Using index condition; Using where
1 SIMPLE Queues_1 eq_ref PRIMARY PRIMARY 4 rtfet.main.Queue 1 Using where
1 SIMPLE Groups_2 ref groups1,groups2,groups3,Domain groups1 259 const 609467 Using where
1 SIMPLE CachedGroupMembers_3 ref DisGrouMem,CachedGroupMembers3,cachedgroupmembers1,SHREDDER_CGM1 DisGrouMem 5 rtfet.Groups_2.id 1 Using where; Using index
second:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE main range Tickets1,Tickets2,Status,Type Type 67 NULL 145945 Using where; Using temporary
1 SIMPLE Queues_1 eq_ref PRIMARY PRIMARY 4 rtfet.main.Queue 1 Using where; Distinct
1 SIMPLE Groups_2 ref groups1,groups2,groups3,Domain groups1 259 const 609467 Using where; Distinct
1 SIMPLE CachedGroupMembers_3 ref DisGrouMem,CachedGroupMembers3,cachedgroupmembers1,SHREDDER_CGM1 DisGrouMem 5 rtfet.Groups_2.id 1 Using where; Using index; Distinct
One problem I can see is the queries being generated are structured inefficiently. You can rewrite the query to parse the same criteria… the simplest fix is changing the “LEFT JOIN Groups Groups_2 …” to “JOIN Groups Groups_2 …” . It’s way faster… backed up by the explain:
---------------------+--------+--------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------------------+--------+------------------------------------------------------------------+------------+---------+-------------------------+--------+--------------------------------------------------------+
| 1 | SIMPLE | Groups_2 | ref | groups1,groups2,groups3,Name,Domain | groups1 | 259 | const | 607330 | Using index condition; Using temporary; Using filesort |
| 1 | SIMPLE | main | eq_ref | PRIMARY,Tickets1,Tickets2,Status,Type | PRIMARY | 4 | rtfet.Groups_2.Instance | 1 | Using where |
| 1 | SIMPLE | Queues_1 | eq_ref | PRIMARY | PRIMARY | 4 | rtfet.main.Queue | 1 | Using where; Distinct |
| 1 | SIMPLE | CachedGroupMembers_3 | ref | DisGrouMem,CachedGroupMembers3,cachedgroupmembers1,SHREDDER_CGM1 | DisGrouMem | 5 | rtfet.Groups_2.id | 1 | Using where; Using index; Distinct |
+------+-------------+----------------------+--------+------------------------------------------------------------------+------------+---------+-------------------------+--------+--------------------------------------------------------+
and you get the same benefits on the other generated queries. Same result set, a tiny fraction of the execution time…
Any idea why we’re using a LEFT JOIN there instead of INNER?
I don’t think tweaking mysql cache variables is going to help here, because the resulting record sets from multiple left joins is going to be too big.
If you assign Global ShowTicket it generates different SQL that executes quickly - but shows users all unassigned tickets, so we cannot use this.