[Rt-devel] slow response opening QueryBuilder in RT 3.6.0

[Mittag, Christian]

Hi,

We are just upgrading from RT 3.0.6 with 60k tickets to RT 3.6.0rc2.
We are using mysql 5.0.22.

If one opens the "Query Builder" for the first time, it takes about
20 sec to generate the list of "owner" (the result: 100 owner from 
2000000 rows).

I saw some discussions about that point, but I have missed the
solution. So, sorry for asking again ...

The problem is a query
"SELECT distinct Users.* FROM
    Users, Principals, CachedGroupMembers, Groups, ACL
    WHERE ACL.RightName = 'OwnTicket' AND ... "
which is done by mysql in the following order:
+--------------------+------+
| table              | rows |
+--------------------+------+
| Users              | 3592 |
| Groups             |  283 |
| Principals         |    1 |
| CachedGroupMembers |    1 |
| ACL                |  329 |
+--------------------+------+
There are about 200k Groups, 7k Users, 90k Principals, 500k 
CachedGroupMembers, 3k ACL
in our case.


If one does the same query but with the tables listed in a different 
order like
"SELECT distinct Users.* FROM
    Groups, CachedGroupMembers, Users, Principals, ACL
    WHERE ACL.RightName = 'OwnTicket' AND ... ",
"explain SELECT" shows:
+--------------------+------+
| table              | rows |
+--------------------+------+
| Groups             |  283 |
| CachedGroupMembers |    2 |
| Users              |    1 |
| Principals         |    1 |
| ACL                |  329 |
+--------------------+------+
That query takes about 0.4 sec.

Is there a way to get that query done with
a straight_join and a given table order or
is there some other fix or workaround to get better
performance generating the list of "owner"?

Thanks!

Christian Mittag