At 10:38 AM 2/09/2005 +1000, you wrote:

>I'm putting a search facility on our intranet, and I've hit a wall.
>My current idea is:
>
>A table of words, with an integer key
>Create table tbl_words (
> int_word integer not null primary key,
> str_word varchar(100) not null unique
>);
>
>
>A table of word id's, linked to an address
>Create table tbl_pageword (
> int_page integer not null,
> int_word integer not null,
> str_href varchar(200) not null,
> primary key(int_page, int_word, str_href)
>);



>The problem is, doing 'and' searches.
>
>For example: find the pages that contain ALL THREE TERMS 'beer',
>'chocolate', '4WD'
>
>Subselects are slower than I'd like, as a new word lookup has to be done for
>each word. For example:
>
>Select str_href from tbl_pageword where
>int_word in (select int_word from tbl_words where str_word = 'beer')
>AND int_word in (select int_word from tbl_words where str_word =
>'chocolate')
>AND int_word in (select int_word from tbl_words where str_word = '4WD');

Because it's an ANDed search, re-entrant inner joins would be cheaper than
the correlated subqueries (I think, but test!)

Select tpw.str_href from tbl_pageword tpw
join tbl_words tw1
on tw1.int_word = tpw.int_word
join tbl_words tw2
on tw2.int_word = tpw.int_word
join tbl_words tw3
on tw3.int_word = tpw.int_word
where tw1.str_word = ? /* string1 */
and tw2.str_word = ? /* string2 */
and tw3.str_word = ? /* string3*/

./heLen