At 10:09 AM 12/03/2005 +0200, you wrote:

>select * from orderitems
>where link in
>(
>select orderitemlink from menu_word_search s
>inner join orderitems_word_match m on m.wordlink = s.link
>where s.word = 'PITA' or s.word = 'CHEESE'
>group by orderitemlink
>having count(*) = 2
>)
>
>The IN query executes on its own in 0.7 seconds.
>As it stands above the orderitems table is read over 8 million times to
>return 82 rows, taking up to 5 minutes!
>I don't understand why firebird doesn't just create a list of values from
>the IN clause and apply it to the table, thereby allowing the impressive
>speeds obtained when the IN is hard coded?

Because the subquery is correlated, it has to be run for each row in
orderitems, i.e. it's not a static IN() list.

>This is part of a full text word indexing system and the count(*) ensures
>that only records containing both words are returned - It would be 3 if
>there were 3 words etc. Menu_word_search contains individual keywords and
>orderitems_word_match contains word matches between the orderitems and
>menu_word_search table.
>Is there another way to structure this query aside from resorting to a SP?

You can avoid the grouping this way, and restrict the correlation:

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

But your intersection structure doesn't seem to help. Why do you need to
link menu_word_search to orderitems at all? A more efficient structure
would be to store the orderitems in one table, the keywords in another, and
form a many:many link between the two tables in the third, viz.

create table orderitems (
orderitem_id integer not null primary key,
searched_text varchar(50),
.....)

create table menu_word_search (
m_w_s integer not null primary key,
word varchar(50)) not null unique);

create table orderitems_word_match (
orderitem_id integer not null,
m_w_s integer not null,
constraint pk_match primary key (orderitem_id, m_w_s),
constraint fk_orderitems foreign key(orderitem_id) references orderitems,
constraint fk_keyword foreign key(m_w_s) references menu_word_search);

Then, you just zip through the search, getting the matching orderitems
using little subqueries to filter them:

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. You can fiddle with this model by giving the FAQ app at
www.ibobjects.com a whirl.

./hb