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