Rajesh Kumar Mallah <[EMAIL PROTECTED]> writes: > rt3=# explain > > SELECT DISTINCT main.* > FROM ((( > (Tickets main JOIN Groups as Groups_1 ON ( main.id = Groups_1.Instance)) > JOIN Principals as Principals_2 ON ( Groups_1.id = Principals_2.ObjectId) > ) JOIN CachedGroupMembers as CachedGroupMembers_3 ON ( Principals_2.id = > CachedGroupMembers_3.GroupId) > ) JOIN Users as Users_4 ON ( CachedGroupMembers_3.MemberId = Users_4.id) > ) > WHERE ((main.EffectiveId = main.id)) > AND ((main.Type = 'ticket')) > AND ((( (Users_4.EmailAddress = '[EMAIL PROTECTED]') > AND (Groups_1.Domain = 'RT::Ticket-Role') > AND (Groups_1.Type = 'Requestor') > AND (Principals_2.PrincipalType = 'Group') > )) > AND ((main.Status = 'new') OR (main.Status = 'open')) > ) > ORDER BY main.Priority DESC LIMIT 10;
So this query seems to be going the long way around to do the equivalent of an IN clause. Presumably because as far as I know mysql didn't support IN subqueries until recently. Can you do an "explain analyze" on the above query and the following rewritten one in 7.4? The "analyze" is important because it'll give real timing information. And it's important that it be on 7.4 as there were improvements in this area specifically in 7.4. SELECT * FROM tickets WHERE id IN ( SELECT groups.instance FROM groups JOIN principals ON (groups.id = principals.objectid) JOIN cachedgroupmembers ON (principals.id = cachedgroupmembers.groupid) JOIN users ON (cachedgroupmembers.memberid = users.id) WHERE users.emailaddress = '[EMAIL PROTECTED]' AND groups.domain = 'RT::Ticket-Role' AND groups.type = 'Requestor' AND principals.principaltype = 'group' ) AND type = 'ticket' AND effectiveid = tickets.id AND (status = 'new' OR status = 'open') ORDER BY priority DESC LIMIT 10; -- greg ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org