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

>
> --- 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.

>

hi one more question

the only downside i can see to using the BookWord architecture you
describe, is that i will be in effect creating a row in this table
for every unique word within every datarow record. is this table
going to become absolutely enormous?