[rt-users] Searching on requestor in large databases

[Matthew Watson]

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