I have a database containing(simplifyinga little) a CUSTOMERS table
andan INVOICES table.
INVOICES contains a foreign key to CUSTOMERS, as you can expect.

I want to retrieve rows showing INVOICES data and some CUSTOMERS data so
I use an inner join, and I want to see newer invoices first so I add an
ORDER BY clause. I only need a few rowssince I will show only the latest
invoices in a grid.
The query is something like

SELECT INVOICES.*, CUSTOMERS.NAME
FROM INVOICES INNER JOIN CUSTOMERS ON INVOICES.CUSTOMER_ID =
CUSTOMERS.CUSTOMER_ID
ORDER BY INVOICES.YEAR DESC, INVOICES.NUMBER DESC

I have created a descending index on INVOICES.YEAR, INVOICES.NUMBER

The problem is that if the tables are large the query is rather slow.
Looking at the plan I see:

PLAN SORT (JOIN (CUSTOMERS NATURAL, INVOICES INDEX (FK_INVOICES_CUSTOMERS)))

so the database loads all rows and then it sorts them.Of course it is slow.


If I test a query like

SELECT * FROM INVOICES
ORDER BY INVOICES.YEAR DESC, INVOICES.NUMBER DESC

the plan is

PLAN (INVOICESORDER IDX_INVOICES_DESC)

and the query is much faster since it uses the index and it only reads a
few rows.


So it looks like if there is an inner join the optimizer does not use
the descending index on INVOICES to speed up things.


Is there a way to force the optimizer to use the descending index
instead of sorting all the resulting rows?
I am using Firebird 2.1 on Windows.

Thanks in advance.

Fulvio Senore