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 _______________________________________________ http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users Community help: http://wiki.bestpractical.com Commercial support: [EMAIL PROTECTED] Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com
