Thank you, using a left outer join did the trick!
Now the query uses the descending index and it is much faster.

I agree that it is not an optimal solution but for this database an
outer join will return the same rows as an inner join (no nulls) so it
solves my problem.

Again, thank you for your quick help.

Fulvio Senore


Il 03/02/2015 13:21, Tim Ward tdw@... [firebird-support] ha scritto:

> You could try an outer join ... mucky in the extreme but it sometimes
> forces the optimiser to look at the sane table first ... (yes I know one
> shouldn't have to do that sort of thing).
>
> On 03/02/2015 12:17, Fulvio Senore mailing@... [firebird-support]
> wrote:
>> 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
>