Slow response opening QueryBuilder in RT 3.6.0

RT Developers
1

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

2

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).

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”?

As a workaround we are using now a special select-query for
just generating the list of owners for the QueryBuilder.
The tables are arranged in an special order just for these queries.
Now mysql 5.0.22 is about 20 times faster.

The wokaround looks like that:

There is a new
lib/RT/Users_special.pm:

package RT::Users_special;
use base qw/RT::Users/;

my %joins = ( ‘Principals Principals_1 CachedGroupMembers
CachedGroupMembers_2 Groups Groups_3 ACL ACL_4’, => ’ Groups G
roups_3, CachedGroupMembers CachedGroupMembers_2, Principals
Principals_1, ACL ACL_4, Users main ',
‘Principals Principals_1 ACL ACL_2 CachedGroupMembers
CachedGroupMembers_3’ => ’ Principals Principals_1,
ACL ACL_2, CachedGroupMembers CachedGroupMembers_3, Users main ',
);

sub _BuildJoins{
my $self = shift;
my $st= join ’ ',@{ $self->{‘aliases’}};

return $joins{$st} if defined $joins{$st};
$self->SUPER::_BuildJoins();

}

1;

This new RT::Users_special is just used in
share/html/Elements/SelectOwner:

— SelectOwner.orig
+++ SelectOwner
@@ -58,6 +58,7 @@

<%INIT>
+use RT::Users_special;
my @objects;

@@ -80,7 +81,7 @@

foreach my $object (@objects) {

  •   my $Users = RT::Users->new($session{CurrentUser});

  •   my $Users = RT::Users_special->new($session{CurrentUser});
   $Users->WhoHaveRight(Right => 'OwnTicket', Object => $object,

IncludeSystemRights => 1, IncludeSuperusers => 0);
while (my $User = $Users->Next()) {

Best regards
Christian Mittag