Hi

I have the following query

SELECT h.INVOICE_NUMBER,h.INVOICE_DATE,h.CUSTOMER_AC_NO, c.CUSTOMER_NAME
FROM ICC_HEADER h join CUSTOMER c on c.ACCOUNT_NO=h.CUSTOMER_AC_NO AND
h.COMPANY=c.CO
WHERE h.company='A'
and h.INVOICE_DATE>='1.4.2006'
and h.INVOICE_DATE<='30.4.2006'
ORDER BY h.ICC_NO DESC

primary key on ICC_HEADER is ICC_NO, primary key on CUSTOMER is
ACCOUNT_NO,CO.

there are 56148 rows in ICC_HEADER and 3511 rows in customer

with no index on INVOICE_DATE I get

Plan
PLAN SORT (JOIN (H NATURAL,C INDEX (RDB$PRIMARY21)))
------ Performance info ------
Prepare time = 0ms
Execute time = 641ms
Avg fetch time = 45.79 ms
Memory buffers = 2,048
Reads from disk to cache = 368
Writes from cache to disk = 0
Fetches from cache = 122,178

This performance is fine even though it reads all 56148 records. BUT with
an index on INVOICE_DATE I get


Plan
PLAN SORT (JOIN (C INDEX (AC_CO_INDEX),H INDEX (ICC_INVDATE_INDEX)))
------ Performance info ------
Prepare time = 0ms
Execute time = 47s 218ms
Avg fetch time = 3,372.71 ms
Memory buffers = 2,048
Reads from disk to cache = 0
Writes from cache to disk = 0
Fetches from cache = 7,815,493

This performance is really bad. It does 3897210 indexed reads on
ICC_HEADER.

The index on INVOICE_DATE is really useful for other queries like SELECT *
FROM ICC_HEADER WHERE INVOICE_DATE='ddd' - why is it so bad in the case
above and why does it need to do 3897210 indexed reads?.

Regards
Paul



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