[rt-devel] When is a row in the User table created?

[Riku Hakkarainen]

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 at domain.com')OR(Users_2.EmailAddress =
'usr at 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 at netspace.net.au