[rt-users] Wishlist emails

[Kenneth Marshall]

On Mon, Feb 18, 2008 at 03:28:43PM +0000, Roy El-Hames wrote:
> 
> 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 
> 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:
>     1- Adding smaller table of Ticket.id and Attachement.Id populated 
> with every new attachement
>     2- Take out Tickets table and use the Transactions.ObjectId as 
> ticket Id's
> 
> In both of the above any customisation will take my RT far away from the 
> base release and wondering if these are addressed in the new version , 
> then I would rather wait.
> I hope the above makes sense and looking forward to the new version.
> 
> Roy
> 
Roy,

The content searching problem really needs to be addressed through
the use of full-text indexing. There is currently an item in the
wiki describing how to use Oracle's full-text support with RT. Here
is the URL: http://wiki.bestpractical.com/view/OracleText. I have
not seen a similar entry for MySQL or PostgreSQL, although I will
be submitting one for PostgreSQL soon. We are upgrading to RT 3.6.x
and PostgreSQL 8.3. 8.3 supports full-text indexing as a core feature.
Maybe with two examples, someone will be able to submit a MySQL version
as well. 

If you are familiar with the full-text index support for your backend
database, you should be able to make these changes yourself. This would
provide much better performance. I think that this solution will scale
much better than just making incremental DB layout improvements. 

Good luck,
Ken