Generated SQL hanging

I’m having trouble figuring out why this:

rt --limit-requestor=foo@bar.com
--limit-requestor=xyzzy@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@bar.com’)
OR (Users_2.EmailAddress = ‘foo@bar.com’)
OR (Watchers_3.Email = ‘xyzzy@quux.net’)
OR (Users_4.EmailAddress = ‘xyzzy@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@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@bar.com’)
OR (Users_2.EmailAddress = ‘foo@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@lafferty.ca -----------±----------------------------------------------

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 :slight_smile:

Mat.

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 :slight_smile:

Mat.

-----Original Message-----
From: rt-devel-admin@lists.fsck.com
[mailto:rt-devel-admin@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@bar.com
--limit-requestor=xyzzy@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@bar.com’)
OR (Users_2.EmailAddress = ‘foo@bar.com’)
OR (Watchers_3.Email = ‘xyzzy@quux.net’)
OR (Users_4.EmailAddress = ‘xyzzy@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@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@bar.com’)
OR (Users_2.EmailAddress = ‘foo@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@lafferty.ca
-----------±----------------------------------------------


rt-devel mailing list
rt-devel@lists.fsck.com
http://lists.fsck.com/mailman/listinfo/rt-devel


rt-devel mailing list
rt-devel@lists.fsck.com
http://lists.fsck.com/mailman/listinfo/rt-devel

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