Hi,

Please suggest indices and/or modification of this query to get it to
execute as quickly as possible:

select count(*)
from MASTER
where (MASTER.FIELD in ('1', '2', '7', ...long list)
and exists (
select *
from CHILD
where MASTER.PK = CHILD.FK
and CHILD.FIELD in ('a', 'b', 'r', ...long list)
)

I currently get a plan like this:
PLAN (MASTER INDEX (MASTER_FIELD,MASTER_FIELD,MASTER_FIELD,...))
PLAN (CHILD INDEX (CHILD_PK))

where
CHILD_PK is index on CHILD (FK, FIELD)
and
MASTER_FIELD is index on MASTER (FIELD)

The plan does not tell me how Firebird merges/correlates the results of
the two queries.

MASTER has about 1 million records, CHILD 1 (mostly) to 5 (unusual)
records per master record. Both MASTER.FIELD and CHILD.FIELD are very
selective.

Thanks,
Kjell
--
--------------------------------------
Kjell Rilbe
Adressmarknaden AM AB
E-post: kjell.rilbe@...
Telefon: 08-761 06 55
Mobil: 0733-44 24 64