[Rt-devel] RT 3.6.0 - "New Search" SLOW

[Ben Allen]

All,

We're running RT 3.6.0 / FreeBSD 6.1-RELEASE-p2 / MySQL 5.0.22  and have
noticed that doing a "Simple Search" followed by a "New Search" causes the system to
hang up for anywhere from 1 minute to 7 minutes.   (last attempt showed "Time to display: 72.682045" on
the page footer.)


Watching on the database, using 'show full processlist' reveals that the query below is consuming a
large amount of time.

<pre>
SELECT DISTINCT main.* FROM Users main , Principals Principals_1, CachedGroupMembers CachedGroupMembers_2, Groups Groups_3, ACL ACL_4
WHERE ((ACL_4.PrincipalType = Groups_3.Type))
AND ((ACL_4.RightName = 'OwnTicket'))
AND ((CachedGroupMembers_2.MemberId = Principals_1.id))
AND ((Groups_3.id = CachedGroupMembers_2.GroupId))
AND ((Principals_1.Disabled = '0'))
AND ((Principals_1.PrincipalType = 'User'))
AND ((Principals_1.id != '1'))
AND ((main.id = Principals_1.id))
AND ((ACL_4.ObjectType = 'RT::Queue') OR (ACL_4.ObjectType = 'RT::System'))
AND ((Groups_3.Domain = 'RT::Queue-Role') OR (Groups_3.Domain = 'RT::System-Role'))
ORDER BY main.Name ASC


Using MySQL's "explain" tool shows that this query searches every record in the 'Users' table (aliased to main).

mysql> explain SELECT DISTINCT main.* FROM Users main , Principals Principals_1, CachedGroupMembers CachedGroupMembers_2, Groups Groups_3, ACL ACL_4  WHERE ((ACL_4.PrincipalType = Groups_3.Type))
AND ((ACL_4.RightName = 'OwnTicket')) AND ((CachedGroupMembers_2.MemberId = Principals_1.id)) AND ((Groups_3.id = CachedGroupMembers_2.GroupId)) AND ((Principals_1.Disabled = '0'))
AND ((Principals_1.PrincipalType = 'User')) AND ((Principals_1.id != '1')) AND ((main.id = Principals_1.id)) AND ((ACL_4.ObjectType = 'RT::Queue') OR (ACL_4.ObjectType = 'RT::System'))
AND ((Groups_3.Domain = 'RT::Queue-Role') OR (Groups_3.Domain = 'RT::System-Role'))  ORDER BY main.Name ASC;
+----+-------------+----------------------+--------+-------------------------+------------+---------+-------------------------------------+-------+----------------------------------------------+
| id | select_type | table                | type   | possible_keys           | key        | key_len | ref                                 | rows  | Extra                                        |
+----+-------------+----------------------+--------+-------------------------+------------+---------+-------------------------------------+-------+----------------------------------------------+
|  1 | SIMPLE      | main                 | range  | PRIMARY,Users3          | PRIMARY    | 4       | NULL                                | 34228 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | Groups_3             | range  | PRIMARY,Groups1,Groups2 | Groups1    | 67      | NULL                                |   498 | Using where; Using index; Distinct           |
|  1 | SIMPLE      | Principals_1         | eq_ref | PRIMARY                 | PRIMARY    | 4       | rt3.main.id                         |     1 | Using where; Distinct                        |
|  1 | SIMPLE      | CachedGroupMembers_2 | ref    | DisGrouMem,GrouMem      | DisGrouMem | 10      | rt3.Groups_3.id,rt3.Principals_1.id |     1 | Using where; Using index; Distinct           |
|  1 | SIMPLE      | ACL_4                | range  | ACL1                    | ACL1       | 54      | NULL                                |     8 | Using where; Using index; Distinct           |
+----+-------------+----------------------+--------+-------------------------+------------+---------+-------------------------------------+-------+----------------------------------------------+
5 rows in set (0.00 sec)

mysql>

</pre>

I've also witnessed a query which involved "SELECT DISTINCT main.* FROM Users main..." when
attempting to display a ticket.  Using "explain" on this query revealed that it, too, was
searching all of the Users table.



First,  is this the expected behavior?   It appears that MySQL caches the results of this query, at least
for some period of time.

Second,  is there a better way to get this list of information out of the database?  From what I can tell,
it seems to be getting a list of users that are allowed to run searches within RT, and get all of their
information.  Is there another way to get at this data?


Sorry if I'm missing something major in the schema, I'm coming into this somewhat cold.


Thanks.

Ben





-- 
======================================================================
Benjamin Allen                                 benjamin at oitsec.umn.edu
U of MN - OIT

     I try to take one day at a time, but sometimes several
        days attack me at once.  -- Ashleigh Brilliant