[rt-users] Performance Issue - RT 3.xx

[Greg Kuhnert]

I've just received a message from the guy that found the initial query
problem .... I am forwarding a copy here for the benefit of others in
the list: 

-----Original Message-----
From: Jim Crumpler 
Sent: Tuesday, July 15, 2003 1:28 PM
To: Greg Kuhnert
Subject: query speed..


I've made the display ticket about 4 times faster by adding two more
indices to the CachedGroupMembers table.. The number of linear scanned
rows scanned has dropped from 167,000 to about 30 for that query I sent
through before.

This may slow down the ticket update a little.. If its too bad, then
drop the indices called group1 and member1 in CachedGroupMembers.

The queries don't seem to be optimised for tables this large.

Jim..

--- 
(the two commands I typed.)

mysql> create index group1 on CachedGroupMembers (GroupId);
Query OK, 167064 rows affected (9.46 sec)
Records: 167064  Duplicates: 0  Warnings: 0

mysql> create index member1 on CachedGroupMembers (MemberId);
Query OK, 167064 rows affected (10.67 sec)
Records: 167064  Duplicates: 0  Warnings: 0


------------------------------------------------------------------------
--------

mysql> describe 
    -> 
    -> SELECT DISTINCT main.* 
    -> FROM Tickets main
    -> ,Groups Groups_1
    -> ,Principals Principals_2
    -> ,CachedGroupMembers CachedGroupMembers_3
    -> ,Principals Principals_4
    -> ,Users Users_5
    -> 
    -> WHERE ((main.EffectiveId = main.id))
    -> AND ((main.Type = 'ticket'))
    -> AND ( (  ( (Users_5.EmailAddress = 'anthony at comindico.com.au')
AND (Groups_1.Domain = 'RT::Ticket-Role') AND (Groups_1.Type =
'Requestor') AND (Principals_2.PrincipalType = 'Group') )  )
    -> AND ( (main.Status = 'new') OR (main.Status = 'open') ) )
    -> AND Groups_1.Instance = main.id  
    -> AND Groups_1.id = Principals_2.ObjectId  
    -> AND Principals_2.id = CachedGroupMembers_3.GroupId
    -> AND CachedGroupMembers_3.MemberId = Principals_4.id
    -> AND Principals_4.ObjectId = Users_5.id
    -> 
    -> ORDER BY main.Priority DESC LIMIT 10;
+----------------------+--------+-----------------------------------+---
----------+---------+------------------------------+------+-------------
--------------------------------+
| table                | type   | possible_keys                     |
key         | key_len | ref                          | rows | Extra
|
+----------------------+--------+-----------------------------------+---
----------+---------+------------------------------+------+-------------
--------------------------------+
| Users_5              | ref    | PRIMARY,Users3,Users4             |
Users4      |     121 | const                        |    1 | where
used; Using temporary; Using filesort |
| Principals_4         | ref    | PRIMARY,Principals2               |
Principals2 |       5 | Users_5.id                   |   15 |
|
| CachedGroupMembers_3 | ref    | DisGrouMem,GrouMem,group1,member1 |
member1     |       5 | Principals_4.id              |    2 |
|
| Principals_2         | eq_ref | PRIMARY,Principals2               |
PRIMARY     |       4 | CachedGroupMembers_3.GroupId |    1 | where used
|
| Groups_1             | eq_ref | PRIMARY,Groups1,Groups2           |
PRIMARY     |       4 | Principals_2.ObjectId        |    1 | where used
|
| main                 | eq_ref | PRIMARY,Tickets4,Tickets5         |
PRIMARY     |       4 | Groups_1.Instance            |    1 | where used
|
+----------------------+--------+-----------------------------------+---
----------+---------+------------------------------+------+-------------
--------------------------------+
6 rows in set (0.00 sec)