[rt-users] Searching on requestor in large databases

[Matthew Watson]

ok, i've track it down a little further..

the query it really dies on is

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 ((Watchers_1.Scope = 'Ticket')) AND
((Watchers_1.Type = 'Requestor')) AND ((Watchers_1.Email =
'user at email.com')OR(Users_2.EmailAddress = 'user at email.com'))  AND main.id =
Watchers_1.Value  ORDER BY main.Priority DESC LIMIT 25

I tried adding some indexes on various things, and that gave it a little
boost, but it still takes about 10-15 seconds just to display a ticket, and
about 20 seconds to display a listing of all emails by that given user..

Anyone have suggestions for speeding this up?

Mat.

-----Original Message-----
From: rt-users-admin at lists.fsck.com
[mailto:rt-users-admin at lists.fsck.com]On Behalf Of Matthew Watson
Sent: Saturday, 5 January 2002 6:57 AM
To: rt-users at lists.fsck.com
Subject: [rt-users] Searching on requestor in large databases


Heya.

using RT 2.0.8
 I've just spent the night finishing off an import of 250,000 Tickets from
rt1 to rt2, everything is nice, except one major problem, searching on
requestor takes AGES, if you search on one requestor it takes about 15
seconds to return the list, if you accendently search on two different
requestors (which is pretty easy to do) it litrally bring mysql to its knees
(99% cpu usage and no end to the query in site), you can't even press stop
on the browser to fix it up either, as mysql just keeps chugging away at the
query.

 Now this wouldn't be a big problem, except we use this function all the
time to find previous tickets by a given requestor, for each and every
incoming call infact.

I've had a look at the way requestor is handled, and its pretty messy, it no
wonder it takes so long to do the query.. I've tried various things like
adding indexes to owner on the watchers table (this wasn't in the default
schema for mysql, but it is for pgsql, odd) , and in a last ditch effort i'm
trying pgsql to see if its any better at handling this complex query..

Any other ideas on how to improve performace for this one, short of
restructing how requestor is stored I can't think of any. Personally I don't
see why requestor isn't stored as a field in ticket (except it means there
can only be one requestor, but thats ok)

Configuration is as follows:
mysql 3.23
rt 2.0.8
searchbuilder .48
apache + mod_perl


Regards
----------------------------------------------
Matthew Watson
Development, Netspace Online Systems
mwatson at netspace.net.au


_______________________________________________
rt-users mailing list
rt-users at lists.fsck.com
http://lists.fsck.com/mailman/listinfo/rt-users