hi, im so interested about IN clause too,
id like to ask also why:

A) .. where afield in (select bfield from [some millions row])
B) .. where afield in (select distinct bfield .... (some houndreds row in
resultset, same table of A)
C) .. where afield in (select <distinct> bfield ....
A and B/C differs only by primary key/index on bField, aField is always
indexed, not primary key
and really looks (at my eyes) so slow.

i really dont know how to improve it, dont tell me to redesign DB as fb
support did with others before, i know (or i think to know) how to design
DB, and i ckecked it so much before posting here: its ok.. and MS-SQL2005,
with same tables, same data.. is really faster on that query. (ONLY on
that...)

fb is really great (or better than other) for a lot of things.. but, for my
point of view, the IN clause is not so good on fb 2+ (i never used previous
verions)
thanks, marco

2009/12/22 Nico Callewaert <callewaert.nico@...>

>
>
> >> where afield = id
> >> if the afield is indexed, the performance is better.
> >>
> >> where afield in (id)
> >> the performance is same (low) with field indexed or not.
> >>
> >> correct me if i am wrong.
>
> > Sorry, but the optimizer will use an index for IN - both
> > formats of IN - list of values and select statement.
>
> > Cheers,
>
> > Ann
>
> Hi again,
>
> I realize performance is better if there's an index on the field, but since
> the customer can set a filter on every field of the table, that would be
> useless. I just created indexes for a few important fields. The rest of the
> fields don't have index...
>
> Best regards !
> Nico
>
>
> [Non-text portions of this message have been removed]
>
>
>

[Non-text portions of this message have been removed]