[rt-users] Query Speed

[Alex Romanauskas]

After upgrading to 3.4.4 the system is hamging whenever attempting to
update a ticket, people or jumbo screens.  The query that is causing the
problems is:


SELECT DISTINCT main.* FROM Users main , Principals Principals_1, ACL
ACL_2, Groups Groups_3, CachedGroupMembers CachedGroupMembers_4  WHERE
((ACL_2.RightName = 'OwnTicket')) AND ((CachedGroupMembers_4.MemberId =
Principals_1.id)) AND ((Groups_3.id = CachedGroupMembers_4.GroupId)) AND
((Principals_1.Disabled = '0')or(Principals_1.Disabled = '0')) AND
((Principals_1.id != '1')) AND ((main.id = Principals_1.id)) AND  ( (
 ACL_2.PrincipalId = Groups_3.id AND ACL_2.PrincipalType = 'Group'
           AND (   Groups_3.Domain = 'SystemInternal' OR Groups_3.Domain
= 'UserDefined' OR Groups_3.Domain = 'ACLEquivalence'))
 OR ( ( (Groups_3.Domain = 'RT::Queue-Role' AND Groups_3.Instance = 19)
 OR ( Groups_3.Domain = 'RT::Ticket-Role' AND Groups_3.Instance = 40103)
 )  AND Groups_3.Type = ACL_2.PrincipalType) )  AND (ACL_2.ObjectType =
'RT::System' OR  (ACL_2.ObjectType = 'RT::Queue' AND ACL_2.ObjectId =
19) )  ORDER BY main.Name ASC;


The query spends most if it's time in the state "Copy to tmp table"(
around 60 seconds).  Is there any way to reduce the time of this query?
 I have around 50 real users and 3000 total users mainly by ticket
submission.  OwnTicket is only granted to the 50 real users.  There are
20 Groups, but no permissions are granted through the groups as they are
only used to added cc/admincc's to tickets.


Here is an EXPLAIN for the query in question.


+----+-------------+----------------------+--------+-----------------------------------+---------+---------+-----------------------------------+------+----------------------------------------------+
| id | select_type | table                | type   | possible_keys
               | key     | key_len | ref                               |
rows | Extra |
+----+-------------+----------------------+--------+-----------------------------------+---------+---------+-----------------------------------+------+----------------------------------------------+
|  1 | SIMPLE      | main                 | ALL    | PRIMARY,Users3
               | NULL    | NULL    | NULL                              |
3060 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | Principals_1         | eq_ref | PRIMARY
               | PRIMARY | 4       | rtdb.main.id                      |
   1 | Using where; Distinct |
|  1 | SIMPLE      | CachedGroupMembers_4 | ref    |
DisGrouMem,GrouMem,group1,member1 | member1 | 5       |
rtdb.Principals_1.id              |    3 | Using where; Distinct |
|  1 | SIMPLE      | ACL_2                | range  | ACL1
               | ACL1    | 58      | NULL                              |
  64 | Using where; Using index; Distinct |
|  1 | SIMPLE      | Groups_3             | eq_ref |
PRIMARY,Groups1,Groups2           | PRIMARY | 4       |
rtdb.CachedGroupMembers_4.GroupId |    1 | Using where; Distinct |
+----+-------------+----------------------+--------+-----------------------------------+---------+---------+-----------------------------------+------+----------------------------------------------+
5 rows in set (9.03 sec)

-


--
Thanks
Alex


-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.