[rt-users] slow content queries

[Joop van de Wege]

On Mon, 28 Nov 2005 17:57:41 -0500 (EST)
rt-users-request at lists.bestpractical.com wrote:

> I've also been wondering whether it would someday be possible for for 
> DBIx::SearchBuilder to be modified to optionally support PostgreSQL's de 
> facto official full-text search engine, tsearch2?
> 
> A query that generated SQL like this:
> 
> ( (attachments.content ilike '%term1%')  and (attachments.content ilike 
> '%term2%') )
> 
> would wind up looking like this when using tsearch2:
> 
> ( attachments.content_tsearch2_idx @@ to_tsquery('default', 'term1 & 
> term2') )
> 
> where content_tsearch2_idx is a new column (tsearch2 requires a 
> dedicated column for its indexing purposes).
> 
> I suppose the translation could also look like this:
> 
> ( (attachments.content_tsearch2_idx @@ to_tsquery('default', 'term1')) 
> and (attachments.content_tsearch2_idx @@ to_tsquery('default', 'term2')))
I made a modification to SearchBuilder to use the ConText option of
Oracle to search inside attachments. It improves performance a lot. One
thing to watch out for is that in the Querybuilder you select 'Content'
contains 'searchstring'. SearchBuilder translates this into 'where
content="%searchstring%". This kills any index you have on content
obviously so I modified SearchBuilder not to do that anymore. 
Search the archives for my patch or if you can't find it I'll post is
again and time permitting add it to the Wiki.

Joop


-- 
Joop van de Wege <JoopvandeWege at mococo.nl>