Forum OpenACS Q&A: Searching

1: Searching

Posted by Kevin Crosbie on 06/19/02 12:37 PM

Hi all,

I'm trying to write some search functionality for the Ticket Tracker.
The database I'm using is Oracle.

Basically, as in ACS ticket tracker, I want to take a search string,
break the phrase up into separate words, and compare those to
whichever columns I choose from the database.

I had it working a while ago using the pseudo_contains function from
the old ecommerce module, and it was working great, until it started
to get really slow. It seemed to get slow as I added more
tickets. I'm not searching the content of the ticket in
cr_revisions or anything, so I'm not sure why it's getting so slow.

I was just wondering if anyone else had had a similar experience with
writing searches, or if there is a different approach I could use.
Most of the solutions for searching on the forums seem to be for
PostGreSQL. I noticed in the bboard module intermedia is used. Is
this a better way to go?

Cheers,

Kevin

2: Response to Searching (response to 1)

Posted by Michael Bryzek on 06/19/02 07:54 PM

When using pseudo_contains, you probably are executing this pl/sql function once per row in your result set. This function itself is relatively slow and could quite possibly be your bottleneck.

interMedia is an easy alternative to try. For something like ticket tracker, you may want to play with the catalog index type available in Oracle 8.1.7 on. It is useful when you do not need the advanced features of the context index type and when you are indexing small documents. Catalog gives you the benefits of never having to worry about synchronizing your index as they are automatically synchronized at commit time.

http://technet.oracle.com/products/text/x/Tech_Overviews/imt_817.html is a decent overview of the interMedia context and catalog index types along with sample code to get you started.

HTH,
Mike