Hi Helen

Thanks for your time and valuable insights :) Very very very much
appreciated.

> select oi.* from orderitems oi
> join orderitems_word_match ows
> on ows.orderitem_id = oi.orderitem_id
> and exists (
> select 1 from menu_word_search mws1
> where mws1.m_w_s = ows.m_w_s
> and mws1.word = 'PITA' )
> and exists (
> select 1 from menu_word_search mws2
> where mws2.m_w_s = ows.m_w_s
> and mws2.word = 'CHEESE' )
> /* keep going if you like */
> and exists (
> select 1 from menu_word_search mws3
> where mws3.m_w_s = ows.m_w_s
> and mws3.word = 'HUMMUS')
>
> Then you can recycle the keywords for use with many tables, by creating
> just one intersection table for each table that has a searchable text
> field
> in it.

This is pretty much the way I have structured the data, but I think my first
query was waaaaaay off base. The second example you gave works very well,
with sub second query times. The only problem is that it only works for
single word matches.

[ORDERITEMS]
LINK ITEM
-----------------
1 CHEESE PITA
2 CHICKEN PITA

[MENU_WORD_SEARCH]
LINK WORD
------------------
100 CHEESE
101 PITA
102 CHICKEN

[ORDERITEMS_WORD_MATCH]
ORDERITEMLINK WORDSEARCHLINK
--------------------
1 100
1 101
2 101
2 102

>> This works

select oi.* from orderitems oi
join orderitems_word_match ows
on ows.orderitemlink = oi.link
and exists (
select 1 from menu_word_search mws1
where mws1.link = ows.wordlink
and mws1.word = 'PITA' )

>> This doesnt

select oi.* from orderitems oi
join orderitems_word_match ows
on ows.orderitemlink = oi.link
and exists (
select 1 from menu_word_search mws1
where mws1.link = ows.wordlink
and mws1.word = 'PITA' )
and exists (
select 1 from menu_word_search mws1
where mws1.link = ows.wordlink
and mws1.word = 'CHEESE' )

Any further help or suggestions will be greatly appreciated,
TIA
Gary