[rt-devel] Generated SQL hanging

[Jesse Vincent]

Short version:
	The entire watchers subsystem has been gutted for 3.0 and no longer
requires such complex queries to find tickets by requestor address.


On Thu, Jul 18, 2002 at 07:48:55AM +1000, Matthew Watson wrote:
> I have problems with this query too, Although it takes along time on my 
> system for any limit-requestor query.. about 10 seconds when there 
> are no other users connected (much longer when other users are connected
> due to mysql locking).
> 
> Haven't got any solution... but would love to see what comes of this :)
> 
> Mat.
> 
> 
> > -----Original Message-----
> > From: rt-devel-admin at lists.fsck.com
> > [mailto:rt-devel-admin at lists.fsck.com]On Behalf Of Rich Lafferty
> > Sent: Wednesday, 17 July 2002 6:21 AM
> > To: RT Development mailing list
> > Subject: [rt-devel] Generated SQL hanging
> > 
> > 
> > I'm having trouble figuring out why this:
> > 
> >   rt --limit-requestor=foo at bar.com \
> >      --limit-requestor=xyzzy at quux.net  --summary
> > 
> > is hanging (or running longer than a couple of minutes, at least).
> > 
> > It generates this SQL:
> > 
> > SELECT DISTINCT main.* 
> >     FROM Tickets main, Watchers Watchers_1, Watchers Watchers_3  
> >     LEFT JOIN Users as Users_2 ON Watchers_1.Owner = Users_2.id 
> >     LEFT JOIN Users as Users_4 ON Watchers_3.Owner = Users_4.id  
> >     WHERE ((Watchers_3.Type = 'Requestor')) 
> >       AND ((Watchers_3.Scope = 'Ticket')) 
> >       AND ((main.EffectiveId = main.id))
> >       AND ((Watchers_1.Scope = 'Ticket')) 
> >       AND ((Watchers_1.Type = 'Requestor')) 
> >       AND (  (Watchers_1.Email ='foo at bar.com') 
> >           OR (Users_2.EmailAddress = 'foo at bar.com') 
> >           OR (Watchers_3.Email = 'xyzzy at quux.net') 
> >           OR (Users_4.EmailAddress = 'xyzzy at quux.net') )
> >       AND main.id = Watchers_1.Value
> >       AND main.id = Watchers_3.Value  ;
> > 
> > which strikes me as unnecessarily complex, but it's not immediately
> > apparent *where*, because this
> > 
> >   rt --limit-requestor=foo at bar.com --summary
> > 
> > generates
> > 
> > SELECT DISTINCT main.* 
> >     FROM Tickets main, Watchers Watchers_1 
> >     LEFT JOIN Users as Users_2 ON Watchers_1.Owner = Users_2.id  
> >     WHERE ((main.EffectiveId = main.id)) 
> >       AND ((Watchers_1.Scope = 'Ticket'))
> >       AND ((Watchers_1.Type = 'Requestor')) 
> >       AND (  (Watchers_1.Email = 'foo at bar.com') 
> >           OR (Users_2.EmailAddress = 'foo at bar.com') )
> >       AND main.id = Watchers_1.Value
> > 
> > which runs instantly. 
> > 
> > Does anything in there jump out as obviously wrong? The two left joins
> > strike me as odd, but I'm having trouble figuring out why.
> > 
> >   -Rich
> > 
> > -- 
> > Rich Lafferty 
> > --------------+-----------------------------------------------
> >  Ottawa, Ontario, Canada    |  Save the Pacific Northwest Tree Octopus!
> >  http://www.lafferty.ca/    |    http://zapatopi.net/treeoctopus.html
> > rich at lafferty.ca 
> > -----------+-----------------------------------------------
> > 
> > _______________________________________________
> > rt-devel mailing list
> > rt-devel at lists.fsck.com
> > http://lists.fsck.com/mailman/listinfo/rt-devel
> 
> _______________________________________________
> rt-devel mailing list
> rt-devel at lists.fsck.com
> http://lists.fsck.com/mailman/listinfo/rt-devel
> 

-- 
»|« http://www.bestpractical.com/rt  -- Trouble Ticketing. Free.