--- In firebird-support@yahoogroups.com, "Adam" <s3057043@y...>
wrote:

>
> >
> > hi adam thanks for the reply.
> >
> > i am building a denormalised table to facilitate a full text
> search.
> > i need an SP to query this table based on a search string which

the

> > user submits. but i also want to order the results according to
> > ranking before returning any rows. so if a row contains

both "joe"

> > and "bloggs" it will appear further up the results set.
> >
> > the best way that i could see to tackle this problem was using 2
> > stored procedures.
> >
> > SP_search would be reponsible for querying the datatable and

adding

> > data to a dummy 'hits' integer column which will keep a tally of
> how
> > many hits there are (e.g. "joe" and "bloggs" equals 2 hits) this
> > kind of thing:
> >
> > for
> > select * from searchindex where text containing :inputparam1 or
> text
> > containing :inputparam2 ...etc...
>
> containing is probably not a good idea here. Containing queries

can

> not use an index, which means the entire searchindex table will

need

> to be read. When searchindex contains a lot of records, this will
> take a lot of resources (and time) to complete.
>
> This is how I would attack such a problem.
>
> Take for example a library where you want to build a search on a

book

> title.
>
> Whenever you add a book, you need to split the title into each

word.

>
> You have some tables
>
> Book (ID, Title, AuthorID, etc)
>
> You use an insert trigger to populate a words table based on the
> title. It only needs to add new records to this table if it is

infact

> a word not in any other book.
>
> Words (ID, Value)
>
> You then have another table to link books to words.
>
> BookWord (BookID, WordID)
>
> This is really simplified, you would need to have exception tables

so

> words like "The" dont influence search results.
>
> You could then use the stored procedure that separated out words

from

> the input text to come up with particular words records, which you
> can then join to BookWord and then Book.
>
> I am sure without too much heartache you could do some sort of
> ranking system using order by count(*) or something like that.
>
> Adam
>

thanks adam. your solution makes a lot of sense. you are definetely
spot on about the indexing so i think your solution would offer a
much better performance which is what im after!

im going to go away and try to see how this works in reality. as per
my first question, have you got any idea how i can get an SP to
break a search string down into seperate words then dynamically
assign these to local variables which i can use within my SP?

ill post back on here when (if) i make any headway with this. thanks.