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

TIA
Gary