Re: Query puts 7.3.4 on endless loop but 7.4beta5 is fine.

Rajesh Kumar Mallah <mallah(at)trade-india(dot)com> 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 = 'mallah_rajesh(at)yahoo(dot)com')
> 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 = 'mallah_rajesh(at)yahoo(dot)com'
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