I’m trying to see if I can optimise the query the find all the tickets
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
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.
Development, Netspace Online Systems