What happens if you create two indexes, one for CUSTOMER and one for
INVOICETOTAL?
Since CUSTOMER is probably a foreign key, you could skip the index on
CUSTOMER.
If most invoices are non-zero, it's probably quicker to skip the index.

Rick DeBay

-----Original Message-----
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of dr_bentonquest
Sent: Friday, March 02, 2007 1:04 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Index not picked up by optimizer

Hi there,

I have an INVOICES table like this:

create table INVOICES
(
NUMBER integer,
CUSTOMER integer,
INVOICETOTAL numeric(10,2),
...more fields...
)

I set up an index like this:

CREATE INDEX INV_TOTAL on INVOICES(CUSTOMER,INVOICETOTAL);

Now when executing the following query, the index is picked up correctly
in the plan:

SELECT SUM(INVOICETOTAL) FROM INVOICES
WHERE CUSTOMER=123 AND INVOICETOTAL > 0;

However, for the following queries the index is NOT picked up:

SELECT SUM(INVOICETOTAL) FROM INVOICES
WHERE CUSTOMER=123 AND INVOICETOTAL <> 0;

SELECT SUM(INVOICETOTAL) FROM INVOICES
WHERE (CUSTOMER=123) AND (NOT INVOICETOTAL = 0);

I'm currently using FB 1.5.3. Is there any workaround I could use to
solve this? I need the total for invoices where the INVOICETOTAL field
is not zero.

Thanks in advance,

-Benton

Disclaimer: This message (including attachments) is confidential and may be privileged. If you have received it by mistake please notify the sender by return e-mail and delete this message from your system. Any unauthorized use or dissemination of this message in whole or in part is strictly prohibited. Please note that e-mails are susceptible to change. RxStrategies, Inc. shall not be liable for the improper or incomplete transmission of the information contained in this communication or for any delay in its receipt or damage to your system. RxStrategies, Inc. does not guarantee that the integrity of this communication has been maintained nor that this communication is free from viruses, interceptions or interference.