Hi, guys.
Thank you for the feedback. Here is patch that should address
performance problems with queries like:
"Owner = X OR Requestor = Y"
"Requestor = Y OR Status = X"
and other queries where positive search conditions (=, like ...) on
Requestor, Cc, AdminCc or Watchers are joined with other conditions
using OR. It doesn't apply to queries with all ANDs, as well it's not
about searches by links, dates or something else. It's about watchers
and ORs.
--
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
