[rt-users] Wishlist emails

Joop van de Wege JoopvandeWege at mococo.nl
Mon Feb 18 10:54:06 EST 2008


Roy El-Hames wrote:
> Hi ;
> 
> I have seen a couple of emails in the past a couple of months about wish 
> list etc which I assume for a new major release, and I have a couple of 
> wishes wondering if these can be looked into,

> Content searching
> I have tried many hacks to get an efficient content searching working, 
> however with a 40G db mostly Attachment table (~ 22G in size) and 
> Transactions table is not small either the content searching takes on 
> average 15 minutes , we have a quad processor 3.8 GHz systems with 16G 
> mem, the db is optimised, doing select * from Attachment where content 
> like '%my search string%' returns in 5 minutes for the same query that 
With wildcards in front of your search you'll always force the db NOT to 
  use indices so performance will always be 'sub' optimal.
The way I fixed this is by changing the source NOT to pre/post pend 
wildcards but let the user to this depending on its needs. This cuts on 
the non-indexed queries alot.

> may take over 15 minutes from the front end (and sometimes comes back 
> with 0 tickets, my guess most the processing is building the join 
> between Tickets, Transactions and Attachments, and I am wondering if the 
> join can be eliminated:
You'll need to capture the sql responsible for this and get an 'explain 
plan' (Oracle term) and see where most of time is spend. From memory I 
think it was doing full table scan of tickets, but I would need to check 
that.

Joop



More information about the rt-users mailing list