I did what you say. CUSTOMER is a FK indeed, so I dropped the compound
index and created a new index containing only the INVOICETOTAL field.

When trying this:

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

only the FK index is picked up. The INVOICETOTAL index is ignored.

Thanks,

-Benton

--- In firebird-support@yahoogroups.com, "Rick Debay" <rdebay@...> wrote:
>
> 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.
>