--- In firebird-support@yahoogroups.com, anand wrote:

> select count(*)
> FROM "Client" WHERE "Client"."Client No" in
> (
> SELECT DISTINCT "Booking"."Client No" FROM "Booking"
> WHERE "Booking"."End Date" >= '02-feb-2001' AND
> "Booking"."Start Date" <= '02-may-2001'
> )
> Plan:
> PLAN (Booking INDEX (IDX_Booking3,IDX_Booking))
> PLAN (Client NATURAL)

??? Why doesn't client use an index for "Client No"? Have you defined
several indexes covering "Client"."Client No" so that Arnos optimizer
gets confused?

By the way, if "Client No" is distinct for any record in client, then
you can simply do

select count(distinct "Client"."Client No")
FROM "Client"
JOIN "Booking" on "Booking"."Client No" = "Client"."Client No"
WHERE "Booking"."End Date" between '02-feb-2001' AND '02-may-2001' and
"Booking"."Start Date" '02-feb-2001' AND '02-may-2001'

> |> the DISTINCT is unneeded here.
>
> there can be more than one booking for one client so
> distinct reomove the duplicate rec in the sub query.

When Arno says you can remove the distinct, you can remove the
distinct. No-one knows the optimizer better than him.

> i tried Exists it works similar to in statement very
> slow.

Show us the definition of all indexes and keys in these tables, exists
should be fast if your indexes are properly defined (though slow and
fast are relative terms - how slow is slow?).

Set