I believe that's what Brian was saying.
(from a follow-up email from [EMAIL PROTECTED]):
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 <[EMAIL PROTECTED]> 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: [EMAIL PROTECTED]
Discover RT's hidden secrets with RT Essentials from O'Reilly Media.
Buy a copy at http://rtbook.bestpractical.com
_______________________________________________
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
