The clause IN executes on every row, all the moment when is compared.
As an alternative you can use the clause EXISTS, this execute only on
the first time. Depending the logic your programm it can be used.

Roberto Novakosky

--- In firebird-support@yahoogroups.com, "Gary Benade" <hobbit@l...>
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?
> 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