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)

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"                                                  
         <kerrboy@gmail.co                                             
         m>                                                         To 
         Sent by:                  rt-users@lists.bestpractical.com    
         rt-users-bounces@                                          cc 
         lists.bestpractic                                             
         al.com                                                Subject 
                                   [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: sales@bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

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

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

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 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 | Users_3 | index | PRIMARY,Users3 | Users3 | 125 | NULL | 2725 | Using index |
| 1 | SIMPLE | CachedGroupMembers_2 | ref | PRIMARY,DisGrouMem,GrouMem,MemberId | MemberId | 5 | rt3.Users_3.id | 2 | Using where |
| 1 | SIMPLE | Groups_1 | eq_ref | PRIMARY,Groups1,Groups2,Groups3 | PRIMARY | 4 | rt3.CachedGroupMembers_2.GroupId | 1 | Using where |
| 1 | SIMPLE | main | eq_ref | PRIMARY,Tickets4,Tickets5 | PRIMARY | 4 | rt3.Groups_1.Instance | 1 | Using where |
4 rows in set (0.03 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 (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.

Nicholas Clark

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

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@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)


The rt-users Archives

Community help: http://wiki.bestpractical.com
Commercial support: sales@bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

Best regards, Ruslan.

I believe that’s what Brian was saying.

(from a follow-up email from kerrboy@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:

I believe that’s what Brian was saying.
I don’t, the original query Brian sent is different and selects only
by Requestor and Status properties. The most important part of that
query is ORs everywhere. If the query is built using the query builder
and that’s what Brian wants then it’s ok and we go on with performance
problem. But if the query is generated in our code then we must start
looking for a bug as I don’t see a reason why we generate it instead
of something like:
“Requestor.EmailAddress = X AND ( Status = ‘new’ OR Status = ‘open’ )”

(from a follow-up email from kerrboy@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…
It’s related and I’m trying to figure out if we can improve
performance without loosing fixes I’ve added.

Can people send me explains for the following two queries:
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);

explain SELECT 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
AND Users_3.EmailAddress LIKE ‘%username%’
WHERE main.Status != ‘deleted’

AND ( ( Users_3.id IS NOT NULL )
OR main.Status = ‘new’ OR main.Status = ‘open’)
AND (main.Type = ‘ticket’)
AND (main.EffectiveId = main.id);

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.

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)


The rt-users Archives

Community help: http://wiki.bestpractical.com
Commercial support: sales@bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com


The rt-users Archives

Community help: http://wiki.bestpractical.com
Commercial support: sales@bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

Best regards, Ruslan.

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.

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)

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 |
1685 | Using index |
| 1 | SIMPLE | Groups_1 | ref |
Groups1,Groups2,idx_RT_1 | Groups2 | 65 | const
| 38962 | 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)

mysql> explain SELECT 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
→ AND Users_3.EmailAddress LIKE ‘%username%’
→ WHERE main.Status != ‘deleted’
→ AND ( Users_3.id IS NOT NULL );
| id | select_type | table | type | possible_keys
| key | key_len | ref |
rows | Extra |
| 1 | SIMPLE | Groups_1 | ref |
Groups1,Groups2,idx_RT_1 | Groups2 | 65 | const
| 39468 | 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 | 5 | rt3.Groups_1.id
| 1 | Using index |
| 1 | SIMPLE | Users_3 | eq_ref | PRIMARY,Users3
| PRIMARY | 4 | rt3.CachedGroupMembers_2.MemberId |
1 | Using where |
4 rows in set (0.00 sec)

Brian Kerr 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

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%’) ) )

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.

The above queries were done with status delimiters.
what is “status delimiter”?

Narrowing the search by including Status in the query. Status has
nothing to do with the problem I’m describing. Forget about it - it
isn’t relevant. My original email included status in the database
query and that does not match up to the query I said I built. Sorry
for the confusion.

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.

Yes. It is using OR. What about the query looks useless to you? I’m
looking for any ticket owned by ‘tom’ or created by ‘tom’.

I could make it owned by ‘tom’, or created by ‘tom@domain.com’, either
way, same result.

The query isn’t useless, it displays all tickets that tom created and tom owns!

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.

It sure is. It still triggers the bad SQL. Regardless of whether or
not status is added to the query.

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%’) ) )


The rt-users Archives

Community help: http://wiki.bestpractical.com
Commercial support: sales@bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

Best regards, Ruslan.

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.

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.

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%’ );

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)