Re: Search for text in Database

Greg Cyrus wrote:

>Hi,
>I would like to use keywords inside a record to search for -
>searchengine-functionality
>How can I design the table? Right now I would store data in CLOB/BLOB or
>Memo-Column in a format like ";apple;banana;lemon;". I would use a
>INSTR(ring)-function - but I wonder if the performance is still OK then -
>even when I index the field. Are there maybe even better ways?
>
>Thanx in advance.
>Greg
>
>
>
>

Greg, have you considered adding a second table that includes a record for each search term? Your design would be

MainObject (Object Id)
ObjectSearchTerms (ObjectId (FK), SearchTerm)

Each MainObject record could have many ObjectSearchTerms. Index the SearchTerm column and performance should be grand. If you have to do Instr in your search, make sure you create a function based index that uses that Instr function. Note that this gets around the problem that some databases do not let you index memo-type columns. Received on Wed Oct 06 2004 - 20:47:22 CDT