Hi Kjell!

If MASTER.FIELD is indeed selective as you say, then I'd say your
query is a good one, it is often a slow experience using COUNT in
databases like Firebird.

The subquery is executed once for every master that matches the
restriction on MASTER.FIELD (at least that is how I view it, Arno and
Ann knows the correct way of saying the same thing).

The one thing you could try, is to use a join rather than an exists, I
have no idea whether it will be faster or slower (well, I'd guess
marginally slower):

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

HTH,
Set

--- In firebird-support@yahoogroups.com, Kjell Rilbe wrote:
> 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@a...
> Telefon: 08-761 06 55
> Mobil: 0733-44 24 64