Hi,

> when i run the sql without index it runs fast if i use the index its
> dead slow.
>
> SQL:
>
> 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'
> )

Could you show us the PLAN returned by the server?

btw, the DISTINCT is unneeded here.

>
> I've index on "Booking"."End Date" & "Booking"."Start Date".

Do you meant a compound index or two different indices?

> I use Delphi 6 & Firebird 1.5
>
> plan analyzer say that the query uses "Booking"."End Date" and
> "Booking"."Client No" and NOT "Booking"."Start Date"
>
> how come i can make it faster?

If "Booking"."End Date" and "Booking"."Start Date" has it's own index i
expected to see that one too.

You could turn your query in a EXISTS which in fact is the same as IN (if
DISTINCT is removed)

SELECT
count(*)
FROM
"Client"
WHERE
EXISTS(SELECT 1 FROM "Booking"
WHERE
"Booking"."End Date" >= '02-feb-2001' AND
"Booking"."Start Date" <= '02-may-2001' AND
"Client"."Client No" = "Booking"."Client No")

Then my question are :

- May i say that "Booking"."End Date" is never later as '02-may-2001'
(because you want start date earlier as '02-may-2001') ?
- May i say that "Booking"."Start Date" is never earlier as '02-feb-2001'
(because you want end date later as '02-feb-2001') ?

If yes then you could speed up your query by using the indices optimal :

SELECT
count(*)
FROM
"Client"
WHERE
EXISTS(SELECT 1 FROM "Booking"
WHERE
"Booking"."End Date" >= '02-feb-2001' AND
"Booking"."End Date" <= '02-may-2001' AND
"Booking"."Start Date" >= '02-feb-2001' AND
"Booking"."Start Date" <= '02-may-2001' AND
"Client"."Client No" = "Booking"."Client No")


Regards,
Arno Brinkman
ABVisie

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Firebird open source database (based on IB-OE) with many SQL-99 features :
http://www.firebirdsql.org
http://www.firebirdsql.info
http://www.fingerbird.de/
http://www.comunidade-firebird.org/

Support list for Interbase and Firebird users :
firebird-support@yahoogroups.com

Nederlandse firebird nieuwsgroep :
news://80.126.130.81