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
