Hi, brave users of RT 3.6.4.
In the mentioned version of RT I changed SQL queries we generate to
searches tickets by watchers(requestors, cc or admicc). Logic of those
queries is better now. However, I've broken performance of queries
like "Requestor = 'X' OR Owner = 'Y'", "Requestor = X OR Status = 'Y'"
and most probably other where condition on watcher is joined with
another condition using OR aggregator.
We have two ways to fix the problem. I sent one patch already to the
list, but we really need more feedback (positive or negative) to make
right choice, so I post it again.
Please do the following steps to collect feedback I need.
1) build a query (see above) in the query builder using sane values
for your setup
2) execute search and wait for results, we expect it to be very slow,
so you shouldn't wait to much
3) apply the patch
cd /opt/rt3;
cat /path/to/the/patch | patch -p0
4) stop and start web server
5) try search again
Send feedback. People who know how to deal with slow-logs, explain are
more than welcome to send additional info, but even if you don't know
how to deal with those it's ok, just send description of the system
behavior and wallclock timings.
6) revert patch
cat /path/to/the/patch | patch -p0 -R
7) stop and start web server
--
Best regards, Ruslan.
=== lib/RT/Tickets_Overlay.pm
==================================================================
--- lib/RT/Tickets_Overlay.pm (revision 5413)
+++ lib/RT/Tickets_Overlay.pm (local)
@@ -854,6 +854,8 @@
$self->_OpenParen;
if ( $op =~ /^IS(?: NOT)?$/ ) {
my $group_members = $self->_GroupMembersJoin( GroupsAlias => $groups );
+ # to avoid joining the table Users into the query, we just join GM
+ # and make sure we don't match records where group is member of itself
$self->SUPER::Limit(
LEFTJOIN => $group_members,
FIELD => 'GroupId',
@@ -954,16 +956,26 @@
);
}
- $self->_SQLLimit(
- ALIAS => $users,
- FIELD => $rest{SUBKEY},
- VALUE => $value,
- OPERATOR => $op,
- CASESENSITIVE => 0,
+ # we join users table without adding some join condition between tables,
+ # the only conditions we have are conditions on the table iteslf,
+ # for example Users.EmailAddress = 'x'. We should add this condition to
+ # the top level of the query and bundle it with another similar conditions,
+ # for example "Users.EmailAddress = 'x' OR Users.EmailAddress = 'Y'".
+ # To achive this goal we use own SUBCLAUSE for conditions on the users table.
+ $self->SUPER::Limit(
%rest,
+ SUBCLAUSE => '_sql_u_watchers_'. $users,
+ ALIAS => $users,
+ FIELD => $rest{'SUBKEY'},
+ VALUE => $value,
+ OPERATOR => $op,
+ CASESENSITIVE => 0,
);
+ # A condition which ties Users and Groups (role groups) is a left join condition
+ # of CachedGroupMembers table. To get correct results of the query we check
+ # if there are matches in CGM table or not using 'cgm.id IS NOT NULL'.
$self->_SQLLimit(
- ENTRYAGGREGATOR => 'AND',
+ %rest,
ALIAS => $group_members,
FIELD => 'id',
OPERATOR => 'IS NOT',
_______________________________________________
http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
Community help: http://wiki.bestpractical.com
Commercial support: [EMAIL PROTECTED]
Discover RT's hidden secrets with RT Essentials from O'Reilly Media.
Buy a copy at http://rtbook.bestpractical.com
