<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<style type="text/css" style="display:none"><!--P{margin-top:0;margin-bottom:0;} .ms-cui-menu {background-color:#ffffff;border:1px rgb(166, 166, 166) solid;font-family:'Segoe UI','Segoe WP','Segoe UI WPC',Tahoma,Arial,sans-serif;font-size:10pt;color:rgb(51, 51, 51);} .ms-cui-menusection-title {display:none;} .ms-cui-ctl {vertical-align:text-top;text-decoration:none;color:rgb(51, 51, 51);} .ms-cui-ctl-on {background-color:rgb(205, 230, 247);opacity: 0.8;} .ms-cui-img-cont-float {display:inline-block;margin-top:2px} .ms-cui-smenu-inner {padding-top:0px;} .ms-owa-paste-option-icon {margin: 0px 6px 0px 6px;vertical-align:middle!important;padding-bottom: 2px;display:inline-block;} .ms-rtePasteFlyout-option:hover {background-color:rgb(205, 230, 247) !important;opacity:1 !important;} .ms-rtePasteFlyout-option {padding:8px 4px 8px 4px;outline:none;} .ms-cui-menusection {float:left; width:85px;height:24px;overflow:hidden}.wf {speak:none; font-weight:normal; font-variant:normal; text-transform:none; -webkit-font-smoothing:antialiased; vertical-align:middle; display:inline-block;}.wf-family-owa {font-family:'o365Icons'}@font-face {  font-family:'o365IconsIE8';  src:url('prem/15.0.995.29/resources/styles/office365icons.ie8.eot?#iefix') format('embedded-opentype'),         url('prem/15.0.995.29/resources/styles/office365icons.ie8.woff') format('woff'),         url('prem/15.0.995.29/resources/styles/office365icons.ie8.ttf') format('truetype');  font-weight:normal;  font-style:normal;}@font-face {  font-family:'o365IconsMouse';  src:url('prem/15.0.995.29/resources/styles/office365icons.mouse.eot?#iefix') format('embedded-opentype'),         url('prem/15.0.995.29/resources/styles/office365icons.mouse.woff') format('woff'),         url('prem/15.0.995.29/resources/styles/office365icons.mouse.ttf') format('truetype');  font-weight:normal;  font-style:normal;}.wf-family-owa {font-family:'o365IconsMouse'}.ie8 .wf-family-owa {font-family:'o365IconsIE8'}.ie8 .wf-owa-play-large:before {content:'\e254';}.notIE8 .wf-owa-play-large:before {content:'\e054';}.ie8 .wf-owa-play-large {color:#FFFFFF/*$WFWhiteColor*/;}.notIE8 .wf-owa-play-large {border-color:#FFFFFF/*$WFWhiteColor*/; width:1.4em; height:1.4em; border-width:.1em; border-style:solid; border-radius:.8em; text-align:center; box-sizing:border-box; -moz-box-sizing:border-box; padding:0.1em; color:#FFFFFF/*$WFWhiteColor*/;}.ie8 .wf-size-play-large {width:40px; height:40px; font-size:30px}.notIE8 .wf-size-play-large {width:40px; height:40px; font-size:30px}.notIE8 .wf-owa-triangle-down-small:before {content:'\e052';}.ie8 .wf-owa-triangle-down-small:before { content:'\e052';}.ie8 .wf-owa-triangle-down-small {color:#666666/*$WFGreyColor*/;}.wf-size-x20 {font-size: 20px!important;}--></style>
</head>
<body dir="ltr" style="font-size:12pt;color:#000000;background-color:#FFFFFF;font-family:Calibri,Arial,Helvetica,sans-serif;">
<p>I've just updated the development instance of our RT service, which had been running at 4.0.8, to the latest 4.2.9 in preparation for a planned production update to follow. Unfortunately, we've hit a significant snag: the UI is now hugely slow in some views
 that were relatively snappy before the update. The slow query log identifies a likely culprit:</p>
<p><br>
</p>
<p># Query_time: 23  Lock_time: 0  Rows_sent: 28  Rows_examined: 858133<br>
SELECT DISTINCT main.* FROM Users main CROSS JOIN ACL ACL_3 JOIN Principals Principals_1  ON ( Principals_1.id = main.id ) JOIN CachedGroupMembers CachedGroupMembers_2  ON ( CachedGroupMembers_2.MemberId = Principals_1.id ) JOIN CachedGroupMembers CachedGroupMembers_4 
 ON ( CachedGroupMembers_4.MemberId = Principals_1.id )  WHERE ((ACL_3.ObjectType = 'RT::Queue' AND ACL_3.ObjectId   = 3) OR (ACL_3.ObjectType = 'RT::System' AND ACL_3.ObjectId   = 1)) AND (ACL_3.PrincipalId = CachedGroupMembers_4.GroupId) AND (ACL_3.PrincipalType
 = 'Group') AND (ACL_3.RightName = 'OwnTicket' OR ACL_3.RightName = 'SuperUser') AND (CachedGroupMembers_2.Disabled = '0') AND (CachedGroupMembers_2.GroupId = '4') AND (CachedGroupMembers_4.Disabled = '0') AND (Principals_1.Disabled = '0') AND (Principals_1.PrincipalType
 = 'User') AND (Principals_1.id != '1')  ORDER BY main.Name ASC;<br>
</p>
<p><br>
</p>
<p>It appears that this query is reasonably well-indexed, but clearly something has changed in the schema updates between 4.0.8 and 4.2.9:</p>
<p><br>
</p>
<p>mysql> explain extended SELECT DISTINCT main.* FROM Users main CROSS JOIN ACL ACL_3 JOIN Principals Principals_1  ON ( Principals_1.id = main.id ) JOIN CachedGroupMembers CachedGroupMembers_2  ON ( CachedGroupMembers_2.MemberId = Principals_1.id ) JOIN CachedGroupMembers
 CachedGroupMembers_4  ON ( CachedGroupMembers_4.MemberId = Principals_1.id )  WHERE ((ACL_3.ObjectType = 'RT::Queue' AND ACL_3.ObjectId   = 3) OR (ACL_3.ObjectType = 'RT::System' AND ACL_3.ObjectId   = 1)) AND (ACL_3.PrincipalId = CachedGroupMembers_4.GroupId)
 AND (ACL_3.PrincipalType = 'Group') AND (ACL_3.RightName = 'OwnTicket' OR ACL_3.RightName = 'SuperUser') AND (CachedGroupMembers_2.Disabled = '0') AND (CachedGroupMembers_2.GroupId = '4') AND (CachedGroupMembers_4.Disabled = '0') AND (Principals_1.Disabled
 = '0') AND (Principals_1.PrincipalType = 'User') AND (Principals_1.id != '1')  ORDER BY main.Name ASC;<br>
+----+-------------+----------------------+--------+----------------------------------------------------+---------------------+---------+------------------------------------+------+-----------------------------------------------------------+<br>
| id | select_type | table                | type   | possible_keys                                      | key                 | key_len | ref                                | rows | Extra                                                     |<br>
+----+-------------+----------------------+--------+----------------------------------------------------+---------------------+---------+------------------------------------+------+-----------------------------------------------------------+<br>
|  1 | SIMPLE      | CachedGroupMembers_2 | range  | DisGrouMem,CachedGroupMembers3,cachedgroupmembers1 | DisGrouMem          | 10      | NULL                               |  606 | Using where; Using index; Using temporary; Using filesort |
<br>
|  1 | SIMPLE      | main                 | eq_ref | PRIMARY                                            | PRIMARY             | 4       | rt34.CachedGroupMembers_2.MemberId |    1 |                                                           |
<br>
|  1 | SIMPLE      | Principals_1         | eq_ref | PRIMARY                                            | PRIMARY             | 4       | rt34.main.id                       |    1 | Using where; Distinct                                     |
<br>
|  1 | SIMPLE      | CachedGroupMembers_4 | ref    | DisGrouMem,CachedGroupMembers3,cachedgroupmembers1 | CachedGroupMembers3 | 5       | rt34.CachedGroupMembers_2.MemberId |    1 | Using where; Distinct                                     |
<br>
|  1 | SIMPLE      | ACL_3                | range  | ACL1                                               | ACL1                | 85      | NULL                               |   13 | Using where; Using index; Distinct                        |
<br>
+----+-------------+----------------------+--------+----------------------------------------------------+---------------------+---------+------------------------------------+------+-----------------------------------------------------------+<br>
5 rows in set, 1 warning (0.00 sec)<br>
</p>
<p><br>
</p>
<p>Can anyone offer any advice on what might have precipitated this sudden drop in query performance?</p>
<p><br>
</p>
<p>Cheers,</p>
<p>Jeff<br>
</p>
</body>
</html>