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
