Forum OpenACS Q&A: Response to some advice on a search...

Collapse
Posted by Alex Sokoloff on
And, since we're worried about performance, the clause:

and lower(jo.title)=lower('%title%')

...will trigger a full table scan. To get a search that uses an index, you need to:

1) create a trigger that populates a column lower_title in job_offers with lower(title)

2) create an index on lower_title

3) restrict the search to beginning of title or full title, and use a clause like:

and jo.lower_title = lower('${title_variable}%')

To be able to search - quickly - for a word anywhere within the title, you'll need to use Neophytos' openfts wizardry which is still very new (or intermedia with Oracle).