When is a row in the User table created?

RT Developers
1

Heya ppls.

I’m trying to see if I can optimise the query the find all the tickets
requested
by a certain person.

SELECT DISTINCT main.* FROM Tickets main,
Watchers Watchers_1 LEFT JOIN Users as Users_2
ON Watchers_1.Owner = Users_2.id
WHERE ((Watchers_1.Scope = ‘Ticket’)) AND ((Watchers_1.Type = ‘Requestor’))
AND ((Watchers_1.Email LIKE ‘%mwatson%’)OR(Users_2.EmailAddress LIKE
‘%mwatson%’))
AND main.id = Watchers_1.Value
ORDER BY main.id ASC LIMIT 50

the “OR” really slows down the query alot, and it currently takes over 10
seconds on my
database to run (after tuning my database). Dropping the OR speeds up the
request, however it
doesn’t pick up all the tickets by that user, as they dont seem to always
have an entry in Users table.

So the question is, when are new users created? and when is the Email field
on the Watchers table filled in?

If the Email field on the Watchers table was always filled in, then this
query could be cleaned up a little bit I think.

Any thoughts?

Matthew Watson
Development, Netspace Online Systems
mwatson@netspace.net.au

2

Actually, in recent releases, RT always creates a user object and links
to that from the relevant watcher object. The fact that it didn’t do this
before was somewhat of a normalization violation. Basically the email address
style watchers are only for watchers without RT accounts. (All requestors
have non-privileged accounts automatically created)On Fri, Jan 11, 2002 at 11:41:36AM +1100, Matthew Watson wrote:

Heya ppls.

I’m trying to see if I can optimise the query the find all the tickets
requested
by a certain person.

SELECT DISTINCT main.* FROM Tickets main,
Watchers Watchers_1 LEFT JOIN Users as Users_2
ON Watchers_1.Owner = Users_2.id
WHERE ((Watchers_1.Scope = ‘Ticket’)) AND ((Watchers_1.Type = ‘Requestor’))
AND ((Watchers_1.Email LIKE ‘%mwatson%’)OR(Users_2.EmailAddress LIKE
‘%mwatson%’))
AND main.id = Watchers_1.Value
ORDER BY main.id ASC LIMIT 50

the “OR” really slows down the query alot, and it currently takes over 10
seconds on my
database to run (after tuning my database). Dropping the OR speeds up the
request, however it
doesn’t pick up all the tickets by that user, as they dont seem to always
have an entry in Users table.

So the question is, when are new users created? and when is the Email field
on the Watchers table filled in?

If the Email field on the Watchers table was always filled in, then this
query could be cleaned up a little bit I think.

Any thoughts?

Matthew Watson
Development, Netspace Online Systems
mwatson@netspace.net.au

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

http://www.bestpractical.com/products/rt – Trouble Ticketing. Free.

3

I believe that the patch Simon Cozens submitted for that was included
in 2.0.9On Fri, Jan 11, 2002 at 01:34:23PM +1100, Matthew Watson wrote:

ok, what about when a privilaged user creates an RT, but changes the
requesters address? Is RT meant to creawted a user object for the requester.

Eg, A customer calls up the helpdesk, the helpdesk creates a trouble ticket
for the customer, with the customer as the requester?

I’ve tested this on the RT install I have (2.0.8) and RT doesn’t seem to
create a user object, does it in the newever versions of RT?

Mat.

-----Original Message-----
From: Jesse Vincent [mailto:jesse@bestpractical.com]
Sent: Friday, 11 January 2002 12:20 PM
To: Matthew Watson
Cc: rt-devel@lists.fsck.com
Subject: Re: [rt-devel] When is a row in the User table created?

Actually, in recent releases, RT always creates a user object and links
to that from the relevant watcher object. The fact that it didn’t do this
before was somewhat of a normalization violation. Basically the
email address
style watchers are only for watchers without RT accounts. (All requestors
have non-privileged accounts automatically created)

On Fri, Jan 11, 2002 at 11:41:36AM +1100, Matthew Watson wrote:

Heya ppls.

I’m trying to see if I can optimise the query the find all the tickets
requested
by a certain person.

SELECT DISTINCT main.* FROM Tickets main,
Watchers Watchers_1 LEFT JOIN Users as Users_2
ON Watchers_1.Owner = Users_2.id
WHERE ((Watchers_1.Scope = ‘Ticket’)) AND ((Watchers_1.Type =
‘Requestor’))
AND ((Watchers_1.Email LIKE ‘%mwatson%’)OR(Users_2.EmailAddress LIKE
‘%mwatson%’))
AND main.id = Watchers_1.Value
ORDER BY main.id ASC LIMIT 50

the “OR” really slows down the query alot, and it currently
takes over 10
seconds on my
database to run (after tuning my database). Dropping the OR
speeds up the
request, however it
doesn’t pick up all the tickets by that user, as they dont seem
to always
have an entry in Users table.

So the question is, when are new users created? and when is the
Email field
on the Watchers table filled in?

If the Email field on the Watchers table was always filled in, then this
query could be cleaned up a little bit I think.

Any thoughts?

Matthew Watson
Development, Netspace Online Systems
mwatson@netspace.net.au

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

–
http://www.bestpractical.com/products/rt – Trouble Ticketing. Free.

http://www.bestpractical.com/products/rt – Trouble Ticketing. Free.

4

It’s used for Cc. that chunk is fairly high on my ‘redesign’ list ;)On Fri, Jan 11, 2002 at 02:13:57PM +1100, Matthew Watson wrote:

arh ok, so after 2.0.9 , is Watchers.Email used for anything? I guess
perhaps its used
for CC? or is a user object created for that too?

Mat.

-----Original Message-----
From: Jesse Vincent [mailto:jesse@bestpractical.com]
Sent: Friday, 11 January 2002 1:36 PM
To: Matthew Watson
Cc: Matthew Watson; rt-devel@lists.fsck.com
Subject: Re: [rt-devel] When is a row in the User table created?

I believe that the patch Simon Cozens submitted for that was included
in 2.0.9

On Fri, Jan 11, 2002 at 01:34:23PM +1100, Matthew Watson wrote:

ok, what about when a privilaged user creates an RT, but changes the
requesters address? Is RT meant to creawted a user object for
the requester.

Eg, A customer calls up the helpdesk, the helpdesk creates a
trouble ticket
for the customer, with the customer as the requester?

I’ve tested this on the RT install I have (2.0.8) and RT doesn’t seem to
create a user object, does it in the newever versions of RT?

Mat.

-----Original Message-----
From: Jesse Vincent [mailto:jesse@bestpractical.com]
Sent: Friday, 11 January 2002 12:20 PM
To: Matthew Watson
Cc: rt-devel@lists.fsck.com
Subject: Re: [rt-devel] When is a row in the User table created?

Actually, in recent releases, RT always creates a user object
and links
to that from the relevant watcher object. The fact that it
didn’t do this
before was somewhat of a normalization violation. Basically the
email address
style watchers are only for watchers without RT accounts.
(All requestors
have non-privileged accounts automatically created)

On Fri, Jan 11, 2002 at 11:41:36AM +1100, Matthew Watson wrote:

Heya ppls.

I’m trying to see if I can optimise the query the find all
the tickets
requested
by a certain person.

SELECT DISTINCT main.* FROM Tickets main,
Watchers Watchers_1 LEFT JOIN Users as Users_2
ON Watchers_1.Owner = Users_2.id
WHERE ((Watchers_1.Scope = ‘Ticket’)) AND ((Watchers_1.Type =
‘Requestor’))
AND ((Watchers_1.Email LIKE ‘%mwatson%’)OR(Users_2.EmailAddress LIKE
‘%mwatson%’))
AND main.id = Watchers_1.Value
ORDER BY main.id ASC LIMIT 50

the “OR” really slows down the query alot, and it currently
takes over 10
seconds on my
database to run (after tuning my database). Dropping the OR
speeds up the
request, however it
doesn’t pick up all the tickets by that user, as they dont seem
to always
have an entry in Users table.

So the question is, when are new users created? and when is the
Email field
on the Watchers table filled in?

If the Email field on the Watchers table was always filled
in, then this
query could be cleaned up a little bit I think.

Any thoughts?

Matthew Watson
Development, Netspace Online Systems
mwatson@netspace.net.au

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

–
http://www.bestpractical.com/products/rt – Trouble Ticketing. Free.

–
http://www.bestpractical.com/products/rt – Trouble Ticketing. Free.

http://www.bestpractical.com/products/rt – Trouble Ticketing. Free.

5

Hello,

We’re using 2.0.11 and still having problems with this query. It takes 6 seconds
on our system.

Query_time: 6 Lock_time: 0 Rows_sent: 0 Rows_examined: 146132

SELECT DISTINCT main.* FROM Tickets main, Watchers Watchers_1 LEFT JOIN Users
as Users_2 ON Watchers_1.Owner = Users_2.id WHERE ((main.Status =
‘open’)OR(main.Status = ‘new’)) AND ((main.EffectiveId = main.id)) AND
((Watchers_1.Scope = ‘Ticket’)) AND ((Watchers_1.Type = ‘Requestor’)) AND
((Watchers_1.Email = ‘user@domain.com’)OR(Users_2.EmailAddress =
‘usr@domain.com’)) AND main.id = Watchers_1.Value ORDER BY
main.Priority DESC LIMIT 25;

Riku

Actually, in recent releases, RT always creates a user object and links
to that from the relevant watcher object. The fact that it didn’t do this
before was somewhat of a normalization violation. Basically the email address
style watchers are only for watchers without RT accounts. (All requestors
have non-privileged accounts automatically created)On Fri, Jan 11, 2002 at 11:41:36AM +1100, Matthew Watson wrote:

Heya ppls.

I’m trying to see if I can optimise the query the find all the tickets
requested
by a certain person.

SELECT DISTINCT main.* FROM Tickets main,
Watchers Watchers_1 LEFT JOIN Users as Users_2
ON Watchers_1.Owner = Users_2.id
WHERE ((Watchers_1.Scope = ‘Ticket’)) AND ((Watchers_1.Type = ‘Requestor’))
AND ((Watchers_1.Email LIKE ‘%mwatson%’)OR(Users_2.EmailAddress LIKE
‘%mwatson%’))
AND main.id = Watchers_1.Value
ORDER BY main.id ASC LIMIT 50

the “OR” really slows down the query alot, and it currently takes over 10
seconds on my
database to run (after tuning my database). Dropping the OR speeds up the
request, however it
doesn’t pick up all the tickets by that user, as they dont seem to always
have an entry in Users table.

So the question is, when are new users created? and when is the Email field
on the Watchers table filled in?

If the Email field on the Watchers table was always filled in, then this
query could be cleaned up a little bit I think.

Any thoughts?

Matthew Watson
Development, Netspace Online Systems
mwatson@netspace.net.au

6

arh ok, so after 2.0.9 , is Watchers.Email used for anything? I guess
perhaps its used
for CC? or is a user object created for that too?

Mat.

7

ok, what about when a privilaged user creates an RT, but changes the
requesters address? Is RT meant to creawted a user object for the requester.

Eg, A customer calls up the helpdesk, the helpdesk creates a trouble ticket
for the customer, with the customer as the requester?

I’ve tested this on the RT install I have (2.0.8) and RT doesn’t seem to
create a user object, does it in the newever versions of RT?

Mat.