[rt-users] Re: query problem after 3.4.5 - 3.6.4 upgrade
On 7/23/07, Brian Kerr [EMAIL PROTECTED] wrote: 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. Requestor.EmailAddress LIKE 'tom' OR Owner = 'tom' Here is what the query looks like when formed in the 3.4.5 query builder. It returns results in less than a second. SELECT COUNT(DISTINCT main.id) FROM (((Tickets main JOIN Groups Groups_1 ON ( Groups_1.Instance = main.id)) LEFT JOIN CachedGroupMembers CachedGroupMembers_2 ON ( CachedGroupMembers_2.GroupId = Groups_1.id) AND ( (CachedGroupMembers_2.GroupId != CachedGroupMembers_2.MemberId))) LEFT JOIN Users Users_3 ON ( Users_3.id = CachedGroupMembers_2.MemberId)) WHERE ((Groups_1.Domain = 'RT::Ticket-Role')) AND ((Groups_1.Type = 'Requestor')) AND ((main.EffectiveId = main.id)) AND ((main.Status != 'deleted')) AND ((main.Type = 'ticket')) AND ( ( (main.Owner = '58936') ) AND ( (Users_3.EmailAddress LIKE '%tom%') ) ) ___ 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] Re: query problem after 3.4.5 - 3.6.4 upgrade
On 7/25/07, Brian Kerr [EMAIL PROTECTED] wrote: On 7/23/07, Brian Kerr [EMAIL PROTECTED] wrote: 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. Requestor.EmailAddress LIKE 'tom' OR Owner = 'tom' Here is what the query looks like when formed in the 3.4.5 query builder. It returns results in less than a second. But as far as I can see the query below returns wrong results and that's exactly what we've fixed. The following query finds tickets where requestor's address has 'tom' substring *and* owner is tom. So it doesn't match the condition you build. SELECT COUNT(DISTINCT main.id) FROM (((Tickets main JOIN Groups Groups_1 ON ( Groups_1.Instance = main.id)) LEFT JOIN CachedGroupMembers CachedGroupMembers_2 ON ( CachedGroupMembers_2.GroupId = Groups_1.id) AND ( (CachedGroupMembers_2.GroupId != CachedGroupMembers_2.MemberId))) LEFT JOIN Users Users_3 ON ( Users_3.id = CachedGroupMembers_2.MemberId)) WHERE ((Groups_1.Domain = 'RT::Ticket-Role')) AND ((Groups_1.Type = 'Requestor')) AND ((main.EffectiveId = main.id)) AND ((main.Status != 'deleted')) AND ((main.Type = 'ticket')) AND ( ( (main.Owner = '58936') ) AND ( (Users_3.EmailAddress LIKE '%tom%') ) ) ___ 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] Re: query problem after 3.4.5 - 3.6.4 upgrade
On 7/24/07, Ruslan Zakirov [EMAIL PROTECTED] wrote: Here is what the query looks like when formed in the 3.4.5 query builder. It returns results in less than a second. But as far as I can see the query below returns wrong results and that's exactly what we've fixed. The following query finds tickets where requestor's address has 'tom' substring *and* owner is tom. So it doesn't match the condition you build. Makes sense. You are right that query isn't right in 3.4.5, I didn't see that. Changing the 3.4.5 mysql query to use OR instead of AND works fine. I think that cross join is killing us in 3.6.4. SELECT COUNT(DISTINCT main.id) FROM (((Tickets main JOIN Groups Groups_1 ON ( Groups_1.Instance = main.id)) LEFT JOIN CachedGroupMembers CachedGroupMembers_2 ON ( CachedGroupMembers_2.GroupId = Groups_1.id) AND ( (CachedGroupMembers_2.GroupId != CachedGroupMembers_2.MemberId))) LEFT JOIN Users Users_3 ON ( Users_3.id = CachedGroupMembers_2.MemberId)) WHERE ((Groups_1.Domain = 'RT::Ticket-Role')) AND ((Groups_1.Type = 'Requestor')) AND ((main.EffectiveId = main.id)) AND ((main.Status != 'deleted')) AND ((main.Type = 'ticket')) AND ( ( (main.Owner = '58936') ) AND ( (Users_3.EmailAddress LIKE '%tom%') ) ) ___ 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] Re: query problem after 3.4.5 - 3.6.4 upgrade
On 7/25/07, Brian Kerr [EMAIL PROTECTED] wrote: On 7/24/07, Ruslan Zakirov [EMAIL PROTECTED] wrote: Here is what the query looks like when formed in the 3.4.5 query builder. It returns results in less than a second. But as far as I can see the query below returns wrong results and that's exactly what we've fixed. The following query finds tickets where requestor's address has 'tom' substring *and* owner is tom. So it doesn't match the condition you build. Makes sense. You are right that query isn't right in 3.4.5, I didn't see that. Changing the 3.4.5 mysql query to use OR instead of AND works fine. I think that cross join is killing us in 3.6.4. As far as I know CROSS JOIN must work in the same way as comma ',' according to SQL standard and docs of all DBs we're using as back-ends. Try to change all CROSS JOINs to ',' . SELECT COUNT(DISTINCT main.id) FROM (((Tickets main JOIN Groups Groups_1 ON ( Groups_1.Instance = main.id)) LEFT JOIN CachedGroupMembers CachedGroupMembers_2 ON ( CachedGroupMembers_2.GroupId = Groups_1.id) AND ( (CachedGroupMembers_2.GroupId != CachedGroupMembers_2.MemberId))) LEFT JOIN Users Users_3 ON ( Users_3.id = CachedGroupMembers_2.MemberId)) WHERE ((Groups_1.Domain = 'RT::Ticket-Role')) AND ((Groups_1.Type = 'Requestor')) AND ((main.EffectiveId = main.id)) AND ((main.Status != 'deleted')) AND ((main.Type = 'ticket')) AND ( ( (main.Owner = '58936') ) AND ( (Users_3.EmailAddress LIKE '%tom%') ) ) -- 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] Re: query problem after 3.4.5 - 3.6.4 upgrade
Hello, Brian. Ok, I've forgotten about Status field and we're talking only about Requestor.EmailAddress = tom OR Owner = tom. I think that I've found problem. Here are three queries I want you to compare. Other user can do that too, but please replace main.Owner = with some valid user ID as it's very important for mysql optimizer. The first one is the query we build in 3.6.4: SELECT SQL_NO_CACHE 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; This is one way to fix it: SELECT SQL_NO_CACHE 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 CachedGroupMembers_2.id IS NOT NULL ) AND Users_3.EmailAddress LIKE '%tom%' AND main.Type = 'ticket' AND main.EffectiveId = main.id; Another way to fix the problem: SELECT SQL_NO_CACHE COUNT(DISTINCT main.id) FROM Tickets main 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.GroupId = Groups_1.id LEFT JOIN Users Users_3 ON CachedGroupMembers_2.MemberId = Users_3.id WHERE main.Status != 'deleted' AND main.Type = 'ticket' AND main.EffectiveId = main.id AND ( main.Owner = '58936' OR Users_3.EmailAddress LIKE '%tom%' ); ___ 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] Re: query problem after 3.4.5 - 3.6.4 upgrade
On 7/24/07, Ruslan Zakirov [EMAIL PROTECTED] wrote: Hello, Brian. Ok, I've forgotten about Status field and we're talking only about Requestor.EmailAddress = tom OR Owner = tom. I think that I've found problem. Here are three queries I want you to compare. Other user can do that too, but please replace main.Owner = with some valid user ID as it's very important for mysql optimizer. Looks much better, here are the queries in the same order you sent them: mysql SELECT SQL_NO_CACHE 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; +-+ | COUNT(DISTINCT main.id) | +-+ | 729 | +-+ 1 row in set (2 min 20.39 sec) mysql SELECT SQL_NO_CACHE 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 CachedGroupMembers_2.id IS NOT NULL ) -AND Users_3.EmailAddress LIKE '%tom%' -AND main.Type = 'ticket' -AND main.EffectiveId = main.id; +-+ | COUNT(DISTINCT main.id) | +-+ | 729 | +-+ 1 row in set (1.17 sec) mysql SELECT SQL_NO_CACHE COUNT(DISTINCT main.id) - FROM Tickets main -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.GroupId = Groups_1.id -LEFT JOIN Users Users_3 -ON CachedGroupMembers_2.MemberId = Users_3.id - WHERE -main.Status != 'deleted' -AND main.Type = 'ticket' -AND main.EffectiveId = main.id -AND ( main.Owner = '58936' OR Users_3.EmailAddress LIKE '%tom%' ); +-+ | COUNT(DISTINCT main.id) | +-+ | 729 | +-+ 1 row in set (2.31 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] Re: query problem after 3.4.5 - 3.6.4 upgrade
Ruslan, I tested the last two queries on my PostgreSQL 8.2 database. The original query was taking so long that I killed it: On Wed, Jul 25, 2007 at 02:33:58AM +0400, Ruslan Zakirov wrote: Hello, Brian. Ok, I've forgotten about Status field and we're talking only about Requestor.EmailAddress = tom OR Owner = tom. I think that I've found problem. Here are three queries I want you to compare. Other user can do that too, but please replace main.Owner = with some valid user ID as it's very important for mysql optimizer. The first one is the query we build in 3.6.4: SELECT SQL_NO_CACHE 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; This version took 4.8 seconds without any additional tuning: This is one way to fix it: SELECT SQL_NO_CACHE 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 CachedGroupMembers_2.id IS NOT NULL ) AND Users_3.EmailAddress LIKE '%tom%' AND main.Type = 'ticket' AND main.EffectiveId = main.id; And this one took 7.6 seconds. Just some more data points although I know that it is not MySQL. I did have to remove the SQL_NO_CACHE from the command. Ken Another way to fix the problem: SELECT SQL_NO_CACHE COUNT(DISTINCT main.id) FROM Tickets main 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.GroupId = Groups_1.id LEFT JOIN Users Users_3 ON CachedGroupMembers_2.MemberId = Users_3.id WHERE main.Status != 'deleted' AND main.Type = 'ticket' AND main.EffectiveId = main.id AND ( main.Owner = '58936' OR Users_3.EmailAddress LIKE '%tom%' ); ___ 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
[rt-users] Re: query problem after 3.4.5 - 3.6.4 upgrade
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. 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. -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] Re: query problem after 3.4.5 - 3.6.4 upgrade
On Mon, Jul 23, 2007 at 11:02:10AM -0400, Brian Kerr wrote: 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. 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. This test instance of RT has a couple more indexes, but it doesn't seem to matter for this. I admit to not understanding the SQL fully, but is that LEFT JOIN really necessary, given the 'IS NOT NULL' check? If the LEFT JOIN becomes an inner JOIN, and one adds an index to MemberID then sanity prevails: 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 | Users3 | 125 | NULL | 2725 | Using index | | 1 | SIMPLE | Groups_1 | ref| Groups1,Groups2,Groups3 | Groups3| 130 | const,const| 49336 | Using where | | 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 | ++-+--++---++-++---+-+ 4 rows in set (0.05 sec) mysql alter table CachedGroupMembers add index (MemberId); Query OK, 451242 rows affected (20.25 sec) Records: 451242 Duplicates: 0 Warnings: 0 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 | Users3 | 125 | NULL | 2725 | Using index | | 1 | SIMPLE | Groups_1 | ref| Groups1,Groups2,Groups3 | Groups3| 130 | const,const| 49336 | Using where | | 1 | SIMPLE | main | eq_ref | PRIMARY,Tickets4,Tickets5 | PRIMARY| 4 | rt3.Groups_1.Instance | 1 | Using where | | 1 | SIMPLE | CachedGroupMembers_2 | ref| DisGrouMem,GrouMem,MemberId | DisGrouMem | 10 | rt3.Groups_1.id,rt3.Users_3.id | 1 | Using where | ++-+--++-++-++---+-+ 4 rows in set (0.04 sec) mysql explain SELECT COUNT(DISTINCT main.id) FROM
Re: [rt-users] Re: query problem after 3.4.5 - 3.6.4 upgrade
On Mon, Jul 23, 2007 at 04:59:27PM +0100, Nicholas Clark wrote: mysql 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 ( ( 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); +-+ | COUNT(DISTINCT main.id) | +-+ |2777 | +-+ 1 row in set (12.87 sec) mysql I'm just not sure a: If the query is actually giving the same answer without the LEFT b: If so, how to patch DBIx::SearchBuilder to generate the better query. Actually, turns out that you don't really need the index on MemberId, but it saves about a second here: 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 ) 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 | Groups_1 | ref| PRIMARY,Groups1,Groups2 | Groups2| 65 | const | 58630 | Using where | | 1 | SIMPLE | main | eq_ref | PRIMARY,Tickets4,Tickets5 | PRIMARY| 4 | rt3.Groups_1.Instance | 1 | Using where | | 1 | SIMPLE | CachedGroupMembers_2 | ref| PRIMARY,DisGrouMem,GrouMem | DisGrouMem | 5 | rt3.Groups_1.id | 2 | Using where | | 1 | SIMPLE | Users_3 | eq_ref | PRIMARY,Users3 | PRIMARY| 4 | rt3.CachedGroupMembers_2.MemberId | 1 | Using where | ++-+--++++-+---+---+-+ 4 rows in set (1.44 sec) mysql 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 ( ( 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); +-+ | COUNT(DISTINCT main.id) | +-+ |2777 | +-+ 1 row in set (13.74 sec) Nicholas Clark ___ 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
