[rt-users] Searching on requestor in large databases

[Jesse Vincent]

you might give 2.0.11 a shot. Also note that a bunch of new indices were
added in 2.0.9. 

you might also want to look at the exact queries going through mysql
and get mysql to explain just how it deals with them so you can see
what indices might help.  

how much ram and cpu do you have in that box?


On Sat, Jan 05, 2002 at 06:56:59AM +1100, Matthew Watson wrote:
> 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
> 

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