[rt-users] query problem after 3.4.5 -> 3.6.4 upgrade

[Forrest Blount]

I believe that's what Brian was saying.

(from a follow-up email from kerrboy at gmail.com):
> Requestor.EmailAddress LIKE 'tom' OR Owner = 'tom'
>
> Forming this query in the query builder will create the nasty SQL.  It
> renders the RT instance unusable and all subsequent queries stack up.
Ruslan, is it possible this is related to your update to 
Tickets.Overlay.pm on 2/13/07?

I ask because the code in _WatcherMembershipLimit  seems to be 
responsible for the query Brian pasted...

Ruslan Zakirov wrote:
> Do I understand right that the query is like "Requestor.EmailAddress =
> 'xxx' OR Status = 'new'  OR Status = 'open'"?
> * Note all binary operators (aggregators) are ORs.
>
>
> On 7/23/07, Brian Kerr <kerrboy at gmail.com> wrote:
>> Hi,
>>
>> This ticket system has been upgraded from 2.0.12 -> 3.4.5 -> 3.6.4.
>> Mysql 4.1.22, Centos 4.4, apache 2.0.52, mod_fastcgi.
>>
>> When doing a search by requestor/owner email address now, we are
>> getting a nasty query produced.  Here are the details of the query.
>> Let me know if you need any more information.
>>
>> Thanks,
>> Brian
>>
>> # Query_time: 621  Lock_time: 0  Rows_sent: 1  Rows_examined: 420186151
>> SELECT COUNT(DISTINCT main.id) FROM Tickets main CROSS JOIN Users
>> Users_3 JOIN Groups Groups_1  ON ( Groups_1.Domain = 'RT::Ticket-Role'
>> ) AND ( Groups_1.Type = 'Requestor' ) AND ( Groups_1.Instance =
>> main.id ) LEFT JOIN CachedGroupMembers CachedGroupMembers_2  ON (
>> CachedGroupMembers_2.MemberId = Users_3.id ) AND (
>> CachedGroupMembers_2.GroupId = Groups_1.id )  WHERE (main.Status !=
>> 'deleted') AND ( ( Users_3.EmailAddress LIKE '%username%' AND
>> CachedGroupMembers_2.id IS NOT NULL )  OR main.Status = 'new' OR
>> main.Status = 'open') AND (main.Type = 'ticket') AND (main.EffectiveId
>> = main.id);
>>
>> mysql> explain SELECT COUNT(DISTINCT main.id) FROM Tickets main CROSS
>> JOIN Users Users_3 JOIN Groups Groups_1  ON ( Groups_1.Domain =
>> 'RT::Ticket-Role' ) AND ( Groups_1.Type = 'Requestor' ) AND (
>> Groups_1.Instance = main.id ) LEFT JOIN CachedGroupMembers
>> CachedGroupMembers_2  ON ( CachedGroupMembers_2.MemberId = Users_3.id
>> ) AND ( CachedGroupMembers_2.GroupId = Groups_1.id )  WHERE
>> (main.Status != 'deleted') AND ( ( Users_3.EmailAddress LIKE
>> '%username%' AND CachedGroupMembers_2.id IS NOT NULL )  OR main.Status
>> = 'new' OR main.Status = 'open') AND (main.Type = 'ticket') AND
>> (main.EffectiveId = main.id);
>> +----+-------------+----------------------+--------+---------------------------+------------+---------+--------------------------------+-------+--------------------------+ 
>>
>> | id | select_type | table                | type   | possible_keys
>>         | key        | key_len | ref                            | rows
>>  | Extra                    |
>> +----+-------------+----------------------+--------+---------------------------+------------+---------+--------------------------------+-------+--------------------------+ 
>>
>> |  1 | SIMPLE      | Users_3              | index  | NULL
>>         | Users4     |     121 | NULL                           |
>> 1609 | Using index              |
>> |  1 | SIMPLE      | Groups_1             | ref    | Groups1,Groups2
>>         | Groups2    |      65 | const                          |
>> 10626 | Using where; Using index |
>> |  1 | SIMPLE      | main                 | eq_ref |
>> PRIMARY,Tickets4,Tickets5 | PRIMARY    |       4 |
>> rt3.Groups_1.Instance          |     1 | Using where              |
>> |  1 | SIMPLE      | CachedGroupMembers_2 | ref    |
>> DisGrouMem,GrouMem        | DisGrouMem |      10 |
>> rt3.Groups_1.id,rt3.Users_3.id |     1 | Using where; Using index |
>> +----+-------------+----------------------+--------+---------------------------+------------+---------+--------------------------------+-------+--------------------------+ 
>>
>> 4 rows in set (0.00 sec)
>> _______________________________________________
>> http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
>>
>> Community help: http://wiki.bestpractical.com
>> Commercial support: sales at bestpractical.com
>>
>>
>> Discover RT's hidden secrets with RT Essentials from O'Reilly Media.
>> Buy a copy at http://rtbook.bestpractical.com
>>
>
>