Re: [rt-users] query problem after 3.4.5 - 3.6.4 upgrade
On 7/23/07, Brian Kerr [EMAIL PROTECTED] wrote: On 7/23/07, Ruslan Zakirov [EMAIL PROTECTED] 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. The above queries were done with status delimiters. what is status delimiter? The query breaks whether or not Status is involved. Here is one of I don't care about Status, but I do care about OR. All I'm asking about is: Do you understand that the first query you sent is a search for tickets with requestor X OR Status Y? I'm asking it only because this query looks useless to me. And if it's not the query you built then it's the query we generate and it's a bug. the broken queries without Status. The database query below is directly from Requestor.EmailAddress LIKE 'tom' OR Owner = 'tom' formed in query builder. This is a different type of queries. 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 (main.Owner = '58936' OR ( Users_3.EmailAddress LIKE '%tom%' AND CachedGroupMembers_2.id IS NOT NULL ) ) AND (main.Type = 'ticket') AND (main.EffectiveId = main.id) -- Best regards, Ruslan. ___ 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
[rt-users] query problem after 3.4.5 - 3.6.4 upgrade
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
Re: [rt-users] query problem after 3.4.5 - 3.6.4 upgrade
Your problem may have something to do with the following statement issued in the 3.6.4 release notes: Use 'Watcher = X' inestead of 'Requestor = X OR Cc = X OR AdminCc = X' in the SelfService interface. Both queries do quite the same job, but the former is significantly faster. I ran into query problems as well when trying to search via Requestor, AdminCc, Cc, etc, after upgrading to 3.6.4. James Moseley Brian Kerr [EMAIL PROTECTED] m To Sent by: rt-users@lists.bestpractical.com rt-users-bounces@ cc lists.bestpractic al.comSubject [rt-users] query problem after 3.4.5 - 3.6.4 upgrade 07/23/2007 09:22 AM 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
Re: [rt-users] query problem after 3.4.5 - 3.6.4 upgrade
On 7/23/07, James Moseley [EMAIL PROTECTED] wrote: Your problem may have something to do with the following statement issued in the 3.6.4 release notes: Use 'Watcher = X' inestead of 'Requestor = X OR Cc = X OR AdminCc = X' in the SelfService interface. Both queries do quite the same job, but the former is significantly faster. I ran into query problems as well when trying to search via Requestor, AdminCc, Cc, etc, after upgrading to 3.6.4. These queries are being made from the query builder. There are many different instances when users would need to search based on an email address and said email address would not be a Watcher, or a privileged user for that matter. This part of the query seems to be the problem: FROM Tickets main CROSS JOIN Users Users_3 Thanks, Brian ___ 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
Re: [rt-users] query problem after 3.4.5 - 3.6.4 upgrade
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 -- Best regards, Ruslan. ___ 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
Re: [rt-users] query problem after 3.4.5 - 3.6.4 upgrade
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
Re: [rt-users] query problem after 3.4.5 - 3.6.4 upgrade
Brian Kerr wrote: On 7/23/07, Ruslan Zakirov [EMAIL PROTECTED] 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. The above queries were done with status delimiters. The query breaks whether or not Status is involved. Here is one of the broken queries without Status. The database query below is directly from Requestor.EmailAddress LIKE 'tom' OR Owner = 'tom' formed in query builder. I get the same query out of Oracle when doing this with rt-3.6.4. This is what I copy from Edit Query-Advanced: Requestor.EmailAddress LIKE 'Joop%' AND Owner = 'Joop' Which results in this: 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) JOIN cachedgroupmembers cachedgroupmembers_2 ON (cachedgroupmembers_2.memberid = users_3.ID) AND (cachedgroupmembers_2.groupid = groups_1.ID) WHERE (main.status != 'deleted') AND ((LOWER (users_3.emailaddress) LIKE '%joop%%' AND cachedgroupmembers_2.ID IS NOT NULL ) AND main.owner = '62' ) AND (main.TYPE = 'ticket') AND (main.effectiveid = main.ID) And it is 'expensive' in that it does a full table scan of Tickets because of the %Joop% which will kill any usage of an index on emailaddress. Plan SELECT STATEMENT ALL_ROWSCost: 143 11 SORT GROUP BY Bytes: 110 Cardinality: 1 10 NESTED LOOPS Cost: 143 Bytes: 110 Cardinality: 1 7 NESTED LOOPS Cost: 142 Bytes: 80 Cardinality: 1 4 NESTED LOOPS Cost: 140 Bytes: 65 Cardinality: 1 1 TABLE ACCESS FULL TABLE RT_USER.TICKETS Cost: 138 Bytes: 31 Cardinality: 1 3 TABLE ACCESS BY INDEX ROWID TABLE RT_USER.GROUPS Cost: 2 Bytes: 34 Cardinality: 1 2 INDEX RANGE SCAN INDEX RT_USER.GROUPS3 Cost: 1 Cardinality: 4 6 TABLE ACCESS BY INDEX ROWID TABLE RT_USER.CACHEDGROUPMEMBERS Cost: 2 Bytes: 30 Cardinality: 2 5 INDEX RANGE SCAN INDEX RT_USER.GROUPID_IDX Cost: 1 Cardinality: 2 9 TABLE ACCESS BY INDEX ROWID TABLE RT_USER.USERS Cost: 1 Bytes: 30 Cardinality: 1 8 INDEX UNIQUE SCAN INDEX (UNIQUE) RT_USER.USERS_KEY Cost: 0 Cardinality: 1 Changing %Joop% to Joop% gives a index scan instead of full table scan. This is one of the things changed in our production RT. People need to add explicitly wildcards and they know that they are in for a wait if they ask for %text% ! I don't know your ticket/user count but this query performs quite good, round 200-300msec for 75 rows retrieved. 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 (main.Owner = '58936' OR ( Users_3.EmailAddress LIKE '%tom%' AND CachedGroupMembers_2.id IS NOT NULL ) ) AND (main.Type = 'ticket') AND (main.EffectiveId = main.id) Joop ___ 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
